-1

I have an already existing table which I want to partition, looking at the documentation I see that this cannot happen, so I re-created the table and try to partition that:

CREATE TABLE tableB (LIKE tableA INCLUDING ALL) PARTITION BY range (field1_in_tableA, field2_in_tableA);

but I am getting:

ERROR:  insufficient columns in PRIMARY KEY constraint definition
DETAIL:  PRIMARY KEY constraint on table "tableB" lacks column "field1_in_tableA" which is part of the partition key.

Is there any workaround to this?

1

If you have a primary key, then you can't partition by a key which is not part of the primary key. Do you want a primary key on the new table? If so, you have to either add field1_in_tableA to the primary key, or remove it from the partitioning key.

  • > If you have a primary key, then you can't partition by a key which is not part of the primary key I see. Is this mentioned somewhere in the documentation though? – whatislove882 Feb 3 at 18:57
  • "Unique constraints on partitioned tables must include all the partition key columns. This limitation exists because PostgreSQL can only enforce uniqueness in each partition individually." postgresql.org/docs/current/ddl-partitioning.html – jjanes Feb 3 at 20:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.