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.

richardvil · Accepted Answer · 2020-03-16 14:10:48Z

23

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);

answered Mar 16, 2020 at 14:10

richardvil

3312 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vaggelis Manousakis Over a year ago

Doing so worked for me like a charm and thank you. However, filling up the table with the data failed (using the GUI option Import/Export). Any ideas why would it fail ? Without the partitions I created I can import the CSV data to the table normally.

Collectives™ on Stack Overflow

Adding primary key for partition table in postgresql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related