On Sun, 2023-03-12 at 13:59 -0400, James Robertson wrote: > 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'); Justin has explained what the problem is, let me supply a solution. I think you want subpartitioning, like CREATE TABLE humans ( hash bytea, fname text, dob date ) PARTITION BY LIST (EXTRACT (YEAR FROM dob)); CREATE TABLE humans_2002 PARTITION OF humans FOR VALUES IN (2002) PARTITION BY HASH (hash); CREATE TABLE humans_2002_0 PARTITION OF humans_2002 FOR VALUES WITH (MODULUS 26, REMAINDER 0); [...] CREATE TABLE humans_2002_25 PARTITION OF humans_2002 FOR VALUES WITH (MODULUS 26, REMAINDER 25); and so on for the other years. Yours, Laurenz Albe
