Hey folks,
I am having issues with multicolumn partitioning. For reference I am using
the following link as my guide:
https://www.postgresql.org/docs/devel/sql-createtable.html
To demonstrate my problem, I created a simple table called humans. I want
to partition by the year of the human birth and then the first character of
the hash. So for each year I'll have year*16 partitions. (hex)
CREATE TABLE humans (
hash bytea,
fname text,
dob date
)PARTITION BY RANGE (EXTRACT(YEAR FROM dob),substring(hash::text, 1,
1));
Reading the documentation: "When creating a range partition, the lower
bound specified with FROM is an inclusive bound, whereas the upper bound
specified with TO is an exclusive bound".
However I can't insert any of the following after the first one, because it
says it overlaps. Do I need to do anything different when defining
multi-column partitions?
This works:
CREATE TABLE humans_1968_0 PARTITION OF humans FOR VALUES FROM (1968, '0')
TO (1969, '1');
These fail:
CREATE TABLE humans_1968_1 PARTITION OF humans FOR VALUES FROM (1968, '1')
TO (1969, '2');
CREATE TABLE humans_1968_2 PARTITION OF humans FOR VALUES FROM (1968, '2')
TO (1969, '3');
CREATE TABLE humans_1968_3 PARTITION OF humans FOR VALUES FROM (1968, '3')
TO (1969, '4');
CREATE TABLE humans_1968_4 PARTITION OF humans FOR VALUES FROM (1968, '4')
TO (1969, '5');
CREATE TABLE humans_1968_5 PARTITION OF humans FOR VALUES FROM (1968, '5')
TO (1969, '6');
CREATE TABLE humans_1968_6 PARTITION OF humans FOR VALUES FROM (1968, '6')
TO (1969, '7');
CREATE TABLE humans_1968_7 PARTITION OF humans FOR VALUES FROM (1968, '7')
TO (1969, '8');
CREATE TABLE humans_1968_8 PARTITION OF humans FOR VALUES FROM (1968, '8')
TO (1969, '9');
CREATE TABLE humans_1968_9 PARTITION OF humans FOR VALUES FROM (1968, '9')
TO (1969, 'a');
CREATE TABLE humans_1968_a PARTITION OF humans FOR VALUES FROM (1968, 'a')
TO (1969, 'b');
CREATE TABLE humans_1968_b PARTITION OF humans FOR VALUES FROM (1968, 'b')
TO (1969, 'c');
CREATE TABLE humans_1968_c PARTITION OF humans FOR VALUES FROM (1968, 'c')
TO (1969, 'd');
CREATE TABLE humans_1968_d PARTITION OF humans FOR VALUES FROM (1968, 'd')
TO (1969, 'e');
CREATE TABLE humans_1968_e PARTITION OF humans FOR VALUES FROM (1968, 'e')
TO (1969, 'f');
CREATE TABLE humans_1968_f PARTITION OF humans FOR VALUES FROM (1968, 'f')
TO (1969, 'g');
CREATE TABLE humans_1969_0 PARTITION OF humans FOR VALUES FROM (1969, '0')
TO (1970, '1');
CREATE TABLE humans_1969_1 PARTITION OF humans FOR VALUES FROM (1969, '1')
TO (1970, '2');
CREATE TABLE humans_1969_2 PARTITION OF humans FOR VALUES FROM (1969, '2')
TO (1970, '3');
CREATE TABLE humans_1969_3 PARTITION OF humans FOR VALUES FROM (1969, '3')
TO (1970, '4');
CREATE TABLE humans_1969_4 PARTITION OF humans FOR VALUES FROM (1969, '4')
TO (1970, '5');
CREATE TABLE humans_1969_5 PARTITION OF humans FOR VALUES FROM (1969, '5')
TO (1970, '6');
CREATE TABLE humans_1969_6 PARTITION OF humans FOR VALUES FROM (1969, '6')
TO (1970, '7');
CREATE TABLE humans_1969_7 PARTITION OF humans FOR VALUES FROM (1969, '7')
TO (1970, '8');
CREATE TABLE humans_1969_8 PARTITION OF humans FOR VALUES FROM (1969, '8')
TO (1970, '9');
CREATE TABLE humans_1969_9 PARTITION OF humans FOR VALUES FROM (1969, '9')
TO (1970, 'a');
CREATE TABLE humans_1969_a PARTITION OF humans FOR VALUES FROM (1969, 'a')
TO (1970, 'b');
CREATE TABLE humans_1969_b PARTITION OF humans FOR VALUES FROM (1969, 'b')
TO (1970, 'c');
CREATE TABLE humans_1969_c PARTITION OF humans FOR VALUES FROM (1969, 'c')
TO (1970, 'd');
CREATE TABLE humans_1969_d PARTITION OF humans FOR VALUES FROM (1969, 'd')
TO (1970, 'e');
CREATE TABLE humans_1969_e PARTITION OF humans FOR VALUES FROM (1969, 'e')
TO (1970, 'f');
CREATE TABLE humans_1969_f PARTITION OF humans FOR VALUES FROM (1969, 'f')
TO (1970, 'g');
Thank you for reviewing this problem.
