A primary key or unique index on a partitioned table is rejected
Creating a PRIMARY KEY on a partitioned table fails when the key lacks the partition columnShort 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.
Fix it fast
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"2Match what you see, then jump to its fix
- 1
PRIMARY KEY (id) on a table partitioned BY RANGE (created_at)The partition key column is missing from the keyFix - 2
PARTITION BY RANGE (date_trunc('month', created_at)) together with a unique keyThe partition key uses an expression or functionFix - 3
The 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
- 1
What does the error look like?
Creating a PRIMARY KEY on a partitioned table fails when the key lacks the partition columnCREATE UNIQUE INDEX on a partitioned table is rejectedA 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
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 key3Verify
The partition key definition now lists only column names.
SELECT pg_get_partkeydef('events'::regclass);Confirmed by postgresql.org
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
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
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix