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.
What lands in your log
ERROR: unique constraint on partitioned table must include all partitioning columns
In 10 seconds
- What triggers it
- Create a table partitioned by RANGE on a column (e.g. id), with at least one partition.
- 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.
Fix
What to do right now
Application-level steps for 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);
INSERT INTO batch8_sales(id, region, amount)
VALUES (1, 'north', 10);For this error
See this error live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Every UNIQUE/PRIMARY KEY on a partitioned table must include every partition-key column. Compare the partition key with the proposed index keys.
Partition keys and unique index keys
Show each partitioned table's key definition and every unique index defined on it.
SELECT p.partrelid::regclass AS partitioned_table,
pg_get_partkeydef(p.partrelid) AS partition_key,
i.indexrelid::regclass AS unique_index,
pg_get_indexdef(i.indexrelid) AS index_definition
FROM pg_partitioned_table p
LEFT JOIN pg_index i ON i.indrelid = p.partrelid AND i.indisunique
ORDER BY p.partrelid::regclass::text, i.indexrelid::regclass::text;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
- Fix that isn't actually a workaround: exact SQL, output, and verdict
- A manual-grounded production interpretation of the lab result
Card required. Cancel before day 7 and you are not charged.
Runbook to fix this
Runbooks for this incident
Full step-by-step fixes for the condition behind this error: the diagnosis, the exact SQL, and output captured in the lab.
- ANALYZE the partitioned parent autovacuum never willPlans across a partitioned table get worse for months while every partition looks healthy.Pro
- Detach and attach partitions with minimal lockingDETACH PARTITION queues behind one open reader and takes the parent table down with it.Pro
- Partition a growing table so the planner can pruneEvery query has to consider the whole table because there is nothing to prune.Pro
Connected
Everything this error touches
Every page this SQLSTATE connects to: the concept that explains it, the runbooks that fix it, the parameters you tune to prevent it, and the sibling errors it travels with. All real cross-references. Jump straight in, or open the full interactive map.
Verification
- Last verified
- 2026-07-16 (Docker lab, PostgreSQL 16.14)
- Verification scope
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed
Went further?
Pro unlocks the second lab proof
Free page stops the bleeding. Pro adds the operational test, SQLSTATE audit, and deeper evidence, same error, more certainty.