INSERT ... ON CONFLICT fails because no unique constraint matches the conflict target
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specificationShort answer
PostgreSQL must find a unique index that covers exactly the columns in your ON CONFLICT list. If none does, you get this error. Create the unique index or constraint on those columns, repeat a partial index's WHERE condition, match an expression index exactly, and remember a statement takes one conflict target.
Fix it fast
1Find out which cause you have
List the indexes on the table and compare them, column for column, with the list inside ON CONFLICT (...).
\d my_table SELECT indexdef FROM pg_indexes WHERE tablename = 'my_table';2Match what you see, then jump to its fix
- 1
The listing shows a plain index, a primary key on other columns, or two separate unique indexes, but nothing on the exact column set.No unique index covers exactly those columnsFix - 2
indexdef shows WHERE ... at the end, or a function call such as lower(email) in place of a column.The only matching index is partial, or is built on an expressionFix - 3
Two unique constraints exist, say on email and on username, and you want either one to count as a conflict.You tried to name two different unique rules at onceFix - 4
The unique constraint was created with DEFERRABLE, and the error appears although the columns match.The constraint is DEFERRABLEFix
- 1
What does the error look like?
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
Cause 1 of 4
No unique index covers exactly those columns
1Check
Inference picks unique indexes that contain exactly the columns you name, in any order. A normal index does not count, and two single-column unique indexes do not add up to one two-column rule.
SELECT indexdef FROM pg_indexes WHERE tablename = 'my_table' AND indexdef LIKE '%UNIQUE%';2Fix
Create the unique index on exactly the target columns (after removing duplicate rows), then retry.
CREATE UNIQUE INDEX my_table_a_b_uniq ON my_table (col_a, col_b);
INSERT INTO my_table (col_a, col_b, val) VALUES (1, 2, 'x')
ON CONFLICT (col_a, col_b) DO UPDATE SET val = EXCLUDED.val;3Verify
The insert runs and a repeat updates the existing row.
SELECT * FROM my_table WHERE col_a = 1 AND col_b = 2;Confirmed by postgresql.org
Cause 2 of 4
The only matching index is partial, or is built on an expression
1Check
For a partial unique index, the conflict target must repeat a predicate that the index satisfies. For an expression index, the target must give the same expression, not just the column.
SELECT indexdef FROM pg_indexes WHERE tablename = 'my_table';2Fix
Add the same predicate or expression to the target.
-- partial index
INSERT INTO my_table (col_a, is_active) VALUES (1, true)
ON CONFLICT (col_a) WHERE is_active DO NOTHING;
-- expression index on lower(email)
INSERT INTO users (email) VALUES ('A@Example.com')
ON CONFLICT (lower(email)) DO NOTHING;3Verify
The statement no longer raises the error.
SELECT count(*) FROM my_table;Confirmed by postgresql.org
Cause 3 of 4
You tried to name two different unique rules at once
1Check
The ON CONFLICT clause takes one conflict target. With DO UPDATE the target is required, so you cannot cover two separate indexes in one clause.
\d my_table2Fix
If you only need to skip duplicates, leave the target out of DO NOTHING; it then handles conflicts with every usable constraint. If you must update, pick the one rule that identifies the row, or run two statements.
INSERT INTO users (email, username) VALUES ('a@example.com', 'a')
ON CONFLICT DO NOTHING;3Verify
A duplicate on either column is skipped without an error.
SELECT count(*) FROM users;Confirmed by postgresql.org
Cause 4 of 4
The constraint is DEFERRABLE
1Check
Only NOT DEFERRABLE constraints and unique indexes are supported as arbiters.
SELECT conname, condeferrable FROM pg_constraint WHERE conrelid = 'my_table'::regclass AND contype = 'u';2Fix
Replace it with a NOT DEFERRABLE constraint, or add a separate unique index for the upsert.
ALTER TABLE my_table DROP CONSTRAINT my_table_col_a_key,
ADD CONSTRAINT my_table_col_a_key UNIQUE (col_a) NOT DEFERRABLE;3Verify
Retry the upsert.
SELECT conname, condeferrable FROM pg_constraint WHERE conrelid = 'my_table'::regclass;Confirmed by postgresql.org
How do I stop it coming back?
- Create the unique index in the same migration as the upsert code, so the two ship together.
- Copy the index definition from \d output when you write the conflict target.
- Keep one rule per table that identifies a row; use it as the only DO UPDATE target.
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
A UNIQUE constraint in PostgreSQL still lets duplicate rows in when a column is NULL
A table with UNIQUE (a, b) holds several rows where a is the same and b is NULL
PostgreSQL treats two NULLs as different, so a UNIQUE constraint cannot reject rows that repeat a NULL.
3 causes, each with a fixDatabases
INSERT ... ON CONFLICT ... RETURNING gives back no row for the existing record
Application code gets no id back on the second call for the same key
RETURNING only returns rows that were actually inserted or updated.
3 causes, each with a fixDatabases
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 fix