Thread: Oracle to Postgres - Transform Hash Partition

Oracle to Postgres - Transform Hash Partition

From

David Barbour

Date:

06 June, 19:28:20

Hi,

New to this list and to Postgres. Been an Oracle DBA for quite a while and we're moving from Oracle to Postgres.

I have a table that I need to transform into a hash partitioned table. I've gone through all the steps to create a hash partitioned table, with 8 partitions such as

create table idev.assessment_result_2023_dab_part (like idev.assessment_result_2023_dab)partition by hash (district_oid);

CREATE TABLE idev.assessment_result_2023_dab_part_assessment_result_2023_p1 PARTITION OF idev.assessment_result_2023_dab_part
FOR VALUES WITH (modulus 64, remainder 0)

....etc through partition 2023_p8 FOR VALUES WITH (modulus 64, remainder 7)

Now I need to 'attach' the original table. The problem I'm running into is there are no good examples of how to define the values.

I've tried several iterations of various 'for values', 'values', 'for values with', etc. but they all error out.

Here's an example:

alter table idev.assessment_result_2023_dab_part
attach partition idev.assessment_result_2023_dab for values with(modulus 8, remainder 1) to (modulus 8, remainder 7)

ERROR: syntax error at or near "to" LINE 2: ..._2023_dab for values with(modulus 8, remainder 1) to (modulu...

Any assistance would be appreciated.

David A. Barbour

dbarbour@istation.com

(214) 292-4096

Istation

8150 North Central Expressway, Suite 2000

Dallas, TX 75206

www.Istation.com

CONFIDENTIALITY / PROPRIETARY NOTICE:

The information contained in this e-mail, including any attachment(s), is confidential information that may be privileged and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or if you received this message in error, then any direct or indirect disclosure, distribution or copying of this message is strictly prohibited. If you have received this message in error, please notify Istation by calling 866-883-7323 immediately and by sending a return e-mail; delete this message; and destroy all copies, including attachments.

Thank you.

Re: Oracle to Postgres - Transform Hash Partition

From

Christoph Moench-Tegeder

Date:

06 June, 21:41:40

## David Barbour (dbarbour@istation.com):

> Now I need to 'attach' the original table.  The problem I'm running into is
> there are no good examples of how to define the values.

The syntax is the same as with CREATE TABLE ... PARTITION OF, e.g.
ALTER TABLE parent ATTACH TABLE part FOR VALUES WITH (MODULUS m, REMAINDER r);

> Here's an example:
>  alter table idev.assessment_result_2023_dab_part
> attach partition idev.assessment_result_2023_dab for values with(modulus 8,
> remainder 1) to (modulus 8, remainder 7)

There's only one (modulus, remainder) tuple in the partition bound
definition for hash partitions, and always only one partition bound
specification per partition.
Maybe what you actually want is a DEFAULT partition (specified as
PARTITION OF parent DEFAULT), or maybe a completely different approach?

Regards,
Christoph

-- 
Spare Space.

Re: Oracle to Postgres - Transform Hash Partition

From

Muhammad Ikram

Date:

07 June, 08:40:38

Hi David,

Here is another approach. See if it serves your purpose

postgres=# create schema idev;
CREATE SCHEMA
postgres=#
postgres=#
postgres=# CREATE TABLE idev.assessment_result_2023_dab (
district_oid int,
-- other columns go here
column1 numeric,
column2 numeric
);
CREATE TABLE
postgres=#
postgres=#
CREATE TABLE idev.assessment_result_2023_dab_part (
LIKE idev.assessment_result_2023_dab
) PARTITION BY HASH (district_oid);

CREATE TABLE idev.assessment_result_2023_dab_part_2023_p1 PARTITION OF idev.assessment_result_2023_dab_part
FOR VALUES WITH (modulus 64, remainder 0);

CREATE TABLE idev.assessment_result_2023_dab_part_2023_p2 PARTITION OF idev.assessment_result_2023_dab_part
FOR VALUES WITH (modulus 64, remainder 1);

CREATE TABLE idev.assessment_result_2023_dab_part_2023_p3 PARTITION OF idev.assessment_result_2023_dab_part
FOR VALUES WITH (modulus 64, remainder 2);

CREATE TABLE idev.assessment_result_2023_dab_part_2023_p4 PARTITION OF idev.assessment_result_2023_dab_part
FOR VALUES WITH (modulus 64, remainder 3);

CREATE TABLE idev.assessment_result_2023_dab_part_2023_p5 PARTITION OF idev.assessment_result_2023_dab_part
FOR VALUES WITH (modulus 64, remainder 4);

CREATE TABLE idev.assessment_result_2023_dab_part_2023_p6 PARTITION OF idev.assessment_result_2023_dab_part
FOR VALUES WITH (modulus 64, remainder 5);

CREATE TABLE idev.assessment_result_2023_dab_part_2023_p7 PARTITION OF idev.assessment_result_2023_dab_part
FOR VALUES WITH (modulus 64, remainder 6);

CREATE TABLE idev.assessment_result_2023_dab_part_2023_p8 PARTITION OF idev.assessment_result_2023_dab_part
FOR VALUES WITH (modulus 64, remainder 7);
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
postgres=#
postgres=#
postgres=# CREATE TABLE idev.temp_assessment_result_2023_dab AS TABLE idev.assessment_result_2023_dab WITH NO DATA;
CREATE TABLE AS
postgres=#
postgres=#
postgres=# INSERT INTO idev.temp_assessment_result_2023_dab
SELECT * FROM idev.assessment_result_2023_dab;
INSERT 0 0
postgres=#
postgres=#
postgres=# TRUNCATE TABLE idev.assessment_result_2023_dab;
TRUNCATE TABLE
postgres=#
postgres=# ALTER TABLE idev.assessment_result_2023_dab_part
ATTACH PARTITION idev.assessment_result_2023_dab
FOR VALUES WITH (modulus 64, remainder 8);
ALTER TABLE
postgres=#
postgres=#
postgres=# INSERT INTO idev.assessment_result_2023_dab_part
SELECT * FROM idev.temp_assessment_result_2023_dab;
INSERT 0 0
postgres=#
postgres=# DROP TABLE idev.temp_assessment_result_2023_dab;
DROP TABLE
postgres=#
postgres=#

Regrads,

Muhammad Ikram

bitnine

On Thu, Jun 6, 2024 at 11:41 PM Christoph Moench-Tegeder <cmt@burggraben.net> wrote:

## David Barbour (dbarbour@istation.com):

> Now I need to 'attach' the original table. The problem I'm running into is
> there are no good examples of how to define the values.

The syntax is the same as with CREATE TABLE ... PARTITION OF, e.g.
ALTER TABLE parent ATTACH TABLE part FOR VALUES WITH (MODULUS m, REMAINDER r);

> Here's an example:
> alter table idev.assessment_result_2023_dab_part
> attach partition idev.assessment_result_2023_dab for values with(modulus 8,
> remainder 1) to (modulus 8, remainder 7)

There's only one (modulus, remainder) tuple in the partition bound
definition for hash partitions, and always only one partition bound
specification per partition.
Maybe what you actually want is a DEFAULT partition (specified as
PARTITION OF parent DEFAULT), or maybe a completely different approach?

Regards,
Christoph

--
Spare Space.

Muhammad Ikram

Re: Oracle to Postgres - Transform Hash Partition

From

Laurenz Albe

Date:

07 June, 11:31:16

On Thu, 2024-06-06 at 11:28 -0500, David Barbour wrote:
> Been an Oracle DBA for quite a while and we're moving from Oracle to Postgres.
>
> I have a table that I need to transform into a hash partitioned table.
> I've gone through all the steps to create a hash partitioned table, with 8 partitions such as 
>
> create table idev.assessment_result_2023_dab_part (like idev.assessment_result_2023_dab)partition by hash
(district_oid);
>
> CREATE TABLE idev.assessment_result_2023_dab_part_assessment_result_2023_p1 PARTITION OF
idev.assessment_result_2023_dab_part
>     FOR VALUES WITH (modulus 64, remainder 0)
>
> ....etc through partition 2023_p8 FOR VALUES WITH (modulus 64, remainder 7)

That won't do.  If you use a modulus of 64, you need 64 partitions, one for
each possible division remainder.

If you want 8 partitions, you have to use modulus 8.

> Now I need to 'attach' the original table.  The problem I'm running into is
> there are no good examples of how to define the values.  
>
> I've tried several iterations of various 'for values', 'values', 'for values with',
> etc. but they all error out.
>
> Here's an example:
>  alter table idev.assessment_result_2023_dab_part
>  attach partition idev.assessment_result_2023_dab for values with(modulus 8, remainder 1) to (modulus 8, remainder 7)
>
> ERROR: syntax error at or near "to" LINE 2: ..._2023_dab for values with(modulus 8, remainder 1) to (modulu...

I think you are confused about hash partitioning.

One partition is only for a single remainder.  You cannot have a partition for
several remainders.

Hash partitioning is mostly for splitting up a table into several parts of
roughly equal size.  You decide how many partitions you want; that will become
the modulus.  Then you have to create that many partitions, one for each remainder.

If you want to attach an existing table as a partition, that will only work if
all rows in the table belong into that partition.  Otherwise, you will get an
error.
So you typically won't be able to attach an existing table as a hash partition.

To convert an existing table into a hash partitioned table, you have to

- create a new, empty partitioned table with *all* its partitions

- transfer the data with "INSERT INTO hash_part_tab SELECT * FROM tab"

Yours,
Laurenz Albe

Re: Oracle to Postgres - Transform Hash Partition - Thanks!

From

David Barbour

Date:

12 June, 01:11:39

First post to this list, and am sure it won't be my last, but you guys rock!

Took a bit from all three replies and got the transformation working seamlessly.

Created temp table.

Copied data into temp from original (just in case!)

Created partitioned table using modulus 8.

Imported the data into the partitioned table.

Dropped the original table and renamed the partitioned table.

Easy, no problems. Also no 'attach'.

Thanks.

On Fri, Jun 7, 2024 at 3:31 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Thu, 2024-06-06 at 11:28 -0500, David Barbour wrote:
> Been an Oracle DBA for quite a while and we're moving from Oracle to Postgres.
>
> I have a table that I need to transform into a hash partitioned table.
> I've gone through all the steps to create a hash partitioned table, with 8 partitions such as
>
> create table idev.assessment_result_2023_dab_part (like idev.assessment_result_2023_dab)partition by hash (district_oid);
>
> CREATE TABLE idev.assessment_result_2023_dab_part_assessment_result_2023_p1 PARTITION OF idev.assessment_result_2023_dab_part
> FOR VALUES WITH (modulus 64, remainder 0)
>
> ....etc through partition 2023_p8 FOR VALUES WITH (modulus 64, remainder 7)

That won't do. If you use a modulus of 64, you need 64 partitions, one for
each possible division remainder.

If you want 8 partitions, you have to use modulus 8.

> Now I need to 'attach' the original table. The problem I'm running into is
> there are no good examples of how to define the values.
>
> I've tried several iterations of various 'for values', 'values', 'for values with',
> etc. but they all error out.
>
> Here's an example:
> alter table idev.assessment_result_2023_dab_part
> attach partition idev.assessment_result_2023_dab for values with(modulus 8, remainder 1) to (modulus 8, remainder 7)
>
> ERROR: syntax error at or near "to" LINE 2: ..._2023_dab for values with(modulus 8, remainder 1) to (modulu...

I think you are confused about hash partitioning.

One partition is only for a single remainder. You cannot have a partition for
several remainders.

Hash partitioning is mostly for splitting up a table into several parts of
roughly equal size. You decide how many partitions you want; that will become
the modulus. Then you have to create that many partitions, one for each remainder.

If you want to attach an existing table as a partition, that will only work if
all rows in the table belong into that partition. Otherwise, you will get an
error.
So you typically won't be able to attach an existing table as a hash partition.

To convert an existing table into a hash partitioned table, you have to

- create a new, empty partitioned table with *all* its partitions

- transfer the data with "INSERT INTO hash_part_tab SELECT * FROM tab"

Yours,
Laurenz Albe

David A. Barbour

dbarbour@istation.com

(214) 292-4096

Istation

8150 North Central Expressway, Suite 2000

Dallas, TX 75206

www.Istation.com

CONFIDENTIALITY / PROPRIETARY NOTICE:

Thank you.