Adding primary key for partition table in postgresql

Question

CREATE TABLE list_parted (a int, b int) PARTITION BY LIST(a);

CREATE TABLE list_part_1 PARTITION OF list_parted FOR VALUES IN (1, 2, 3);
CREATE TABLE list_part_2 PARTITION OF list_parted FOR VALUES IN (6, 7, 8);

INSERT INTO list_parted VALUES (7, 77);

ALTER TABLE list_parted ADD PRIMARY KEY (b);

When am trying to add the primary key for the above table, I get this error:

ERROR: insufficient columns in PRIMARY KEY constraint definition
SQL state 0A000
Detail: PRIMARY KEY constraint on table "list_parted" lacks column "a" which is part of the partition key.

Answer 1

You need to include the partitioning column in the declaration of the PK or create a UNIQUE idx with both columns, is the same result.

CREATE TABLE customer(
  id int,
  country_code character varying(5),
  name character varying(100),
  PRIMARY KEY (id, country_code)
)
PARTITION BY LIST (country_code);