Re: Oracle to Postgres - Transform Hash Partition

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=#