CREATE TABLE LIKE with different primary key for partitioning

Question

I've got an existing table of dogs which I would like to partition by list using the colour column:

CREATE TABLE dogs (
  id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  colour text,
  name text
)
;

Because it's not possible to partition an existing table, I'm going to make a new empty partitioned table then copy the data across.

CREATE TABLE
trade_capture._customer_invoice
(
  LIKE
  trade_capture.customer_invoice
  INCLUDING ALL
)
PARTITION BY LIST (bill_month)
;

This reports:

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

I know I can ignore the primary key like this, but it seems bad to have a table with no primary key!

CREATE TABLE
_dogs
(
  LIKE
  dogs
  INCLUDING ALL
  EXCLUDING INDEXES
)
PARTITION BY LIST (colour)
;

What's the best way to proceed so I have a partitioned table which still has a primary key?

score 2 · Accepted Answer · answered Nov 02 '20 at 16:24

2

Add the partitioning key to the primary key:

ALTER TABLE trade_capture._customer_invoice
   ADD PRIMARY KEY (id, bill_month);

There is no other option to have a unique constraint on a partitioned table.

answered Nov 02 '20 at 16:24

Laurenz Albe

209,280
17
206
263

1

Thanks for this, and for all your hard work on SQL-related SO. I think that when I post an SQL question here, I'm really thinking to myself "What do Laurenz Albe, a_horse_with_no_name, and Erwin Brandstetter think about this problem?" – LondonRob Nov 03 '20 at 11:35

CREATE TABLE LIKE with different primary key for partitioning

1 Answers1