PostgreSQL10 - is it possible to do PARTITION BY LIST (col1, col2, .., colN)?

Question

I am looking at the PostgreSQL official documentation page on Table Partitioning for my version of postgres.

I would like to create table partitions over three columns, and I wish to use declarative partition with BY LIST method to do that.

However, I cannot seem to find a good example on how to deal with more columns, and BY LIST specifically.

In the aforementioned docs I only read:

You may decide to use multiple columns in the partition key for range partitioning, if desired. (...) For example, consider a table range partitioned using columns lastname and firstname (in that order) as the partition key.

It seems that declarative partition on multiple columns is only for BY RANGE or is that not right?

However, if it is not, I found an answer on SO that tells me how to deal with BY LIST and one column. But in my case I have three columns.

My idea would be to do something like the following (I am pretty sure it's wrong):

CREATE TABLE my_partitioned_table (
    col1 type CONSTRAINT col1_constraint CHECK (col1 = 1 or col1 = 0),
    col2 type CONSTRAINT col2_constraint CHECK (col2 = 'A' or col2 = 'B'),
    col3 type,
    col4 type) PARTITION BY LIST (col1, col2);

CREATE TABLE part_1a PARTITION OF my_partitioned_table
    FOR VALUES IN (1, 'A');

CREATE TABLE part_1b PARTITION OF my_partitioned_tabel
    FOR VALUES IN (1, 'B');

...

I would need a correct implemenation as the combination of possible partitions in my case is quite a lot.

Answer 1

That is true, you cannot use list partitioning with more than one partitioning key. You also cannot bent range partitioning to do what you want.

But you could use a composite type to get what you want:

CREATE TYPE part_type AS (a integer, b text);

CREATE TABLE partme (p part_type, val text) PARTITION BY LIST (p);

CREATE TABLE partme_1_B PARTITION OF partme FOR VALUES IN (ROW(1, 'B'));

INSERT INTO partme VALUES (ROW(1, 'B'), 'x');

INSERT INTO partme VALUES (ROW(1, 'C'), 'x');
ERROR:  no partition of relation "partme" found for row
DETAIL:  Partition key of the failing row contains (p) = ((1,C)).

SELECT (p).a, (p).b, val FROM partme;

 a | b | val 
---+---+-----
 1 | B | x
(1 row)

But perhaps the best way to go is to use subpartitioning: partition the original table by the first column and the partitions by the second column.