Search code examples
postgresqlpostgresql-12

CREATE TABLE LIKE with different primary key for partitioning

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?

Solution

  • 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.