Re: Oracle to Postgres - Transform Hash Partition

[Muhammad Ikram <mmikram@xxxxxxxxx>]

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@xxxxxxxxxxxxxx> wrote:

## David Barbour (dbarbour@xxxxxxxxxxxx):

> 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