Incident brief
Unique constraint on partitioned table
A UNIQUE constraint was added to a partitioned table without including all of the partition key columns. PostgreSQL rejected it because it cannot enforce uniqueness across partitions.
In 10 seconds
- What
- Unique constraint on partitioned table
- What triggers it
- Create a table partitioned by RANGE on a column (e.g. id), with at least one partition.
- The fix
- Include every partition key column in the UNIQUE (or PRIMARY KEY) constraint's column list.
- Proof
- Reproduced on PostgreSQL 16.14 → Adding a UNIQUE constraint that omitted the partition key column was rejected with SQLSTATE 0A000. The same constraint succeeded once the partition key column was included.
The fix
What to do right now
The immediate, application-level response to this error.
- Include every partition key column in the UNIQUE (or PRIMARY KEY) constraint's column list.
- If true cross-partition uniqueness on a non-key column is required, enforce it outside PostgreSQL or reconsider the partitioning key.
ALTER TABLE batch8_sales ADD CONSTRAINT batch8_sales_id_region_unique UNIQUE (id, region);Diagnose
See it live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Standard triage — not specific to this error
These are canonical PostgreSQL system-catalog queries, shown as SQL to run. No sample output is attached because this is general triage, not a captured lab transcript.This SQLSTATE does not have an error-specific live snapshot yet. These are the canonical system-catalog queries you run against the affected server to see the problem in real time — standard triage, not a reproduced transcript.
What is running right now
Active backends, how long each has been running, and what it is waiting on.
SELECT pid,
state,
wait_event_type,
wait_event,
now() - query_start AS running_for,
left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
AND pid <> pg_backend_pid()
ORDER BY running_for DESC NULLS LAST;Who is blocking whom
Turn raw blocking PIDs into the actual queries on both sides of the wait.
SELECT blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity AS blocking ON blocking.pid = b.pid
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;Locks that are still waiting
Every lock a backend has requested but not yet been granted.
SELECT l.pid,
l.locktype,
l.mode,
l.granted,
COALESCE(c.relname, l.transactionid::text) AS object
FROM pg_locks l
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE NOT l.granted
ORDER BY l.pid;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual — not paraphrased.
PostgreSQL 16 Documentation — Table Partitioning, Limitations (5.11.2)
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.Read the full section on postgresql.org →
UNIQUE constraint missing the partition key column
Per the manual, a unique constraint's columns must include every partition key column — PostgreSQL can only enforce uniqueness within each partition's own index, so the partition key itself has to guarantee no duplicates land in different partitions.UNIQUE constraint including the partition key column
Adding the partition key column (id) to the constraint satisfied the requirement, and the constraint was created successfully.Reproduce & verify
A real, single-session PostgreSQL reproduction
A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab — the commands below are exactly what was executed.
- 1Create a table partitioned by RANGE on a column (e.g. id), with at least one partition.
- 2Run ALTER TABLE ... ADD CONSTRAINT ... UNIQUE on a column that does not include the partition key.
- 3SQLSTATE 0A000 is reported, with a DETAIL line naming the missing partition-key column.
One client: a table partitioned by RANGE (id), with one partition. A UNIQUE constraint is added on a column (region) that doesn't include the partition key.
CREATE TABLE batch8_sales (id int, region text, amount numeric) PARTITION BY RANGE (id);
CREATE TABLE batch8_sales_p1 PARTITION OF batch8_sales FOR VALUES FROM (1) TO (1000);ALTER TABLE batch8_sales ADD CONSTRAINT batch8_sales_region_unique UNIQUE (region);ALTER TABLE batch8_sales ADD CONSTRAINT batch8_sales_id_region_unique UNIQUE (id, region);What PostgreSQL actually returned
CREATE TABLE
CREATE TABLEERROR: unique constraint on partitioned table must include all partitioning columns
DETAIL: UNIQUE constraint on table "batch8_sales" lacks column "id" which is part of the partition key.ALTER TABLEThe deeper lab audit for this error
What Pro unlocks here
- The exact prevention SQL — copy-paste ready
- Raw psql output captured from the Docker lab
- A senior-DBA action list to take it further
- Live monitoring queries to catch it in production
- The deeper audit: fix-that-fails counterexample, GUC before/after, server-log evidence
Related & next steps
Follow the thread
Everything this error touches — jump straight to the sibling error, term, runbook, or parameter.
Verification
- Last verified
- 2026-07-16 (Docker lab, PostgreSQL 16.14)
- Reviewed by
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed