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 <c...@burggraben.net>
wrote:
> ## David Barbour (dbarb...@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
