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.

The error message is self-explanatory - what part of it are you having trouble understanding? — Dai, Apr 21 '23 at 08:46
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. — Anuj TBE, Apr 21 '23 at 08:56
It looks like you need to clarify what you need to be unique. Your statement *"`id` column will always be unique"* is contradicted by your `PRIMARY KEY ( "id", scan_time )` definition: the latter allows for multiple entries of the exact same `"id"` as long as they come with different `scan_time`. The answer you accepted also pretty much breaks your `tracking_id` uniqueness: you attempted to define it as globally unique, while the proposed constraint enables duplicates as long as there's even a nanosecond `scan_time` difference between twin entries. — Zegarek, Apr 21 '23 at 09:44

score 1 · Accepted Answer · answered Apr 21 '23 at 09:05

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

PostgreSQL unique constraint on partitioned table must include all partitioning columns

1 Answers1