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

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