Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A primary key or unique index on a partitioned table is rejected

ErrorCreating a PRIMARY KEY on a partitioned table fails when the key lacks the partition column

Short answer

On a partitioned table, every unique or primary key constraint must include all partition key columns, and the partition key cannot use expressions. Each partition's index can only check its own rows. Add the key to the constraint, change the partition key, or enforce global uniqueness another way.

  • Applies to PostgreSQL 16, PostgreSQL 17
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Print the partition key and the constraints, then compare the two lists. Every column in the partition key must be in each unique constraint.

    psql -d mydb -c "SELECT pg_get_partkeydef('events'::regclass);"
    psql -d mydb -c "\d events"
  2. 2Match what you see, then jump to its fix

    1. 1PRIMARY KEY (id) on a table partitioned BY RANGE (created_at)The partition key column is missing from the keyFix
    2. 2PARTITION BY RANGE (date_trunc('month', created_at)) together with a unique keyThe partition key uses an expression or functionFix
    3. 3The key includes the top-level column but still fails on a table partitioned again below itA multi-level hierarchy leaves out a sub-partition columnFix

What does the error look like?

  • Creating a PRIMARY KEY on a partitioned table fails when the key lacks the partition column
  • CREATE UNIQUE INDEX on a partitioned table is rejected
  • A unique key worked on a plain table and fails after partitioning it

Cause 1 of 3

The partition key column is missing from the key

1Check

The manual says the constraint's columns must include all of the partition key columns, because each partition's index can only enforce uniqueness inside its own partition.

SELECT pg_get_partkeydef('events'::regclass);

2Fix

Add the partition column to the key. Uniqueness then means the pair, which is the only thing PostgreSQL can enforce across partitions.

CREATE TABLE events (
    id         bigint NOT NULL,
    created_at timestamptz NOT NULL,
    payload    jsonb,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

3Verify

Creating the table now succeeds.

psql -d mydb -c "\d events"

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

The partition key uses an expression or function

1Check

A unique or primary key constraint requires that the partition key has no expressions or function calls.

SELECT pg_get_partkeydef('events'::regclass);

2Fix

Partition by a plain column. Store the derived value in its own column if you need it.

ALTER TABLE events ADD COLUMN month date;
-- then partition BY RANGE (month) and include month in the key

3Verify

The partition key definition now lists only column names.

SELECT pg_get_partkeydef('events'::regclass);

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

A multi-level hierarchy leaves out a sub-partition column

1Check

For a multi-level hierarchy, list the partition key of each level: every one of them must be in the constraint.

SELECT c.relname, pg_get_partkeydef(c.oid)
FROM pg_class c WHERE c.relkind = 'p';

2Fix

Add each level's column to the constraint, or flatten the hierarchy to one level.

ALTER TABLE ONLY measurement ADD UNIQUE (city_id, logdate);

3Verify

The ALTER TABLE statement finishes without an error.

psql -d mydb -c "\d measurement"

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Choose the partition key before you choose the primary key, and put it into the key.
  • If global uniqueness on id alone is a hard requirement, keep a separate unpartitioned table of ids, or avoid partitioning.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.