Multi-column partitioning in Greenplum

Question

I am trying to do multi-column partitioning in Greenplum database using PostgreSQL. However I keep getting an error -

ERROR: partition key has 2 columns but 1 columns specified in VALUES clause LINE 15: VALUES ('10001','2014-03-11'), ^ ********** Error **********

ERROR: partition key has 2 columns but 1 columns specified in VALUES clause SQL state: 42P16 Character: 341

This is the query that I used:

CREATE TABLE EMP_TABLE
(
  EMP_ID CHARACTER VARYING(9) NOT NULL,
  JOB_ID CHARACTER VARYING(10) NOT NULL,
  DT_OF_JOIN DATE NOT NULL,
  SALARY NUMERIC(20,8) NOT NULL
--  CONSTRAINT ENTITY_MODEL_SCORE_PKEY PRIMARY KEY (ENTITY_ID, MODEL_ID, MODEL_RUN_DT)
)
WITH (
  OIDS=FALSE
)
DISTRIBUTED BY (EMP_ID)
PARTITION BY LIST(EMP_ID,DT_OF_JOIN)
(
VALUES ('10001','2014-03-11'),
VALUES ('10002','2014-03-12')
)

I am not sure what I am missing. Can someone help me with the right syntax to do multi-column partition in Greenplum using PostgreSQL?

buqing · Answer 1 · 2016-01-05T19:15:02.330

You can try it following using subpartition

CREATE TABLE sandbox.EMP_TABLE
(
  EMP_ID CHARACTER VARYING(9) NOT NULL,
  JOB_ID CHARACTER VARYING(10) NOT NULL,
  DT_OF_JOIN date NOT NULL,
  SALARY NUMERIC(20,8) NOT NULL
--  CONSTRAINT ENTITY_MODEL_SCORE_PKEY PRIMARY KEY (ENTITY_ID, MODEL_ID, MODEL_RUN_DT)
)
WITH (
  OIDS=FALSE
)
DISTRIBUTED BY (JOB_ID)
PARTITION BY LIST(EMP_ID)
    SUBPARTITION BY LIST(DT_OF_JOIN) 
    SUBPARTITION TEMPLATE
   (
      SUBPARTITION year1 VALUES ('2014-03-11'),
      SUBPARTITION year2 VALUES ('2014-03-12')
   )
(
    values ('1001'),
    values('10002')
)

Multi-column partitioning in Greenplum

1 Answers1