Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

INSERT ... ON CONFLICT fails because no unique constraint matches the conflict target

ErrorERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Short 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.

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

Fix it fast

  1. 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';
  2. 2Match what you see, then jump to its fix

    1. 1The 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. 2indexdef 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. 3Two 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. 4The unique constraint was created with DEFERRABLE, and the error appears although the columns match.The constraint is DEFERRABLEFix

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

Did this fix it?

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

Did this fix it?

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_table

2Fix

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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

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