Thread: Partition by list - is select possible?

On Wed, 8 Jul 2020 at 22:32, Mike Martin <redtux1@gmail.com> wrote:
>
> Simple question - is it possible to have a select query providing the values for a list partitioned table? ie:
>
> CREATE TABLE testpart (fileid int,tagname text,tagvalue text[])
> PARTITION BY LIST (tagname);
> CREATE TABLE testpart_tag PARTITION OF testpart
>     FOR VALUES  IN (SELECT tag FROM tagmap);
> CREATE TABLE testpart_def PARTITION OF testpart
>     DEFAULT;

The partition clause values must be known during CREATE TABLE / ATTACH
PARTITION. The only way to do that would be to do some dynamic SQL
that reads the values from the table during CREATE TABLE / ATTACH
PARTITION.

In psql, that could look something like:

postgres=# create table t1 (a int);
CREATE TABLE
postgres=# insert into t1 values(1),(2),(3);
INSERT 0 3
postgres=# create table lp (z int) partition by list (z);
CREATE TABLE
postgres=# select 'create table lp123 partition of lp for values in('
|| string_agg(a::text,',') || ');' from t1;
                         ?column?
----------------------------------------------------------
 create table lp123 partition of lp for values in(1,2,3);
(1 row)

postgres=# \gexec
CREATE TABLE

David