PostgreSQL unique constraint on partitioned table must include all partitioning columns

Question

I want to create a partitioned table in the PostgreSQL database and run the below query.

CREATE TABLE tracking_trackingdata (

  "id"                  uuid                     NOT NULL,
  tracking_id           varchar(100)             NOT NULL UNIQUE,
  dynamic_url_object_id bigint                   NOT NULL,
  ip_address            inet                     NOT NULL,
  scan_time             timestamp with time zone NOT NULL,
  created               timestamp with time zone NOT NULL,
  modified              timestamp with time zone NOT NULL,

  PRIMARY KEY ( "id", scan_time )

) PARTITION BY RANGE ( scan_time )

but it keeps on giving the error

[0A000] ERROR: unique constraint on partitioned table must include all partitioning columns Detail: UNIQUE constraint on table tracking_trackingdata lacks column scan_time which is part of the partition key.

The scan_time could be duplicated while the id column will always be unique. I want to have a partition by scan_time, how can I apply a unique constraint on it when there can be duplicate entries at the same time? I have also passed the id and scan_time columns to the PRIMARY KEY constraint so that the combination of both will always be unique.

Answer 1

The error message is misleading: the problem isn't your PRIMARY KEY, it's the UNIQUE constraint on your tracking_id column;

...so if you remove the UNIQUE constraint from the tracking_id then the CREATE TABLE statement succeeds: https://www.db-fiddle.com/f/h4UAZj25KSCS4eKHmFc89x/0

You can still require tracking_id to be unique, just promote the UNIQUE constraint from a single-column to a multi-column constraint. The docs explain why:

https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-LIMITATIONS

To create a unique or primary key constraint on a partitioned table, the partition keys must not include any expressions or function calls and the constraint's columns must include all of the partition key columns. This limitation exists because the individual indexes making up the constraint can only directly enforce uniqueness within their own partitions; therefore, the partition structure itself must guarantee that there are not duplicates in different partitions.

e.g. https://www.db-fiddle.com/f/vHj8XDJyZevcey7A2abiP8/0

This works for me, without errors, in Postgres 15:

CREATE TABLE tracking_trackingdata 
(
  "id"                  uuid                     NOT NULL,
  tracking_id           varchar(100)             NOT NULL,
  dynamic_url_object_id bigint                   NOT NULL,
  ip_address            inet                     NOT NULL,
  scan_time             timestamp with time zone NOT NULL,
  created               timestamp with time zone NOT NULL,
  modified              timestamp with time zone NOT NULL,
  
  CONSTRAINT PK_tracking_trackingdata PRIMARY KEY ( "id", scan_time ),
  CONSTRAINT UK_tracking_id UNIQUE ( tracking_id, scan_time )
  
) PARTITION BY RANGE ( scan_time );