Search code examples
greenplum

How to create multi-column partition table in Greenplum?

How to create an table with multi-column partition keys in Greenplum?

I tried following statement.

CREATE TABLE dump_single_root (col1 varchar, col2 varchar, col3 varchar, col4 varchar, col5 varchar, col6 varchar, col7 varchar, col8 varchar, col9 varchar) 
DISTRIBUTED BY (col1)
PARTITION BY LIST (col4, col7)
( PARTITION buildings VALUES ('13','BUILDING'), 
  PARTITION automobiles VALUES ('12','AUTOMOBILE'), 
  DEFAULT PARTITION other );

But I got following error during execution.

ERROR: partition key has 2 columns but 1 columns specified in VALUES clause

I saw some people suggest to use SUBPARTITION. But I think this is for creating multi-level partition table, and I believe Multi-level partition table and Multi-column partition table are separate topics.

Is there anyone can give me some advice?

Thanks a lot

Solution

  • you need to add values in double braces to run this successfully

    CREATE TABLE dump_single_root 
(col1 varchar, col2 varchar, col3 varchar, col4 varchar, col5 varchar, 
col6     varchar, col7 varchar, col8 varchar, col9 varchar) 
DISTRIBUTED BY (col1)
PARTITION BY LIST (col4, col7)
( PARTITION buildings VALUES (('13','BUILDING')), 
PARTITION automobiles VALUES (('12','AUTOMOBILE')), 
DEFAULT PARTITION other );