PostgreSQL error: no unique or exclusion constraint matching the ON CONFLICT specification

SQLSTATE 42P10 (invalid_column_reference) · Updated July 9, 2026

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

INSERT ... ON CONFLICT needs an arbiter: a unique (or exclusion) constraint that defines what "conflict" means. The conflict target you wrote must match one exactly — same column set, same expression, same partial predicate. No match, no upsert.

What this error means

PostgreSQL does not guess which constraint you mean. ON CONFLICT (email) requires a unique index on exactly (email):

• A non-unique index on email does not count.
• A unique index on (tenant_id, email) does not match a target of (email) — nor the other way around.
• An expression index on lower(email) matches only the target (lower(email)).
• A partial unique index (WHERE deleted_at IS NULL) matches only a target with the same WHERE clause.

Common causes

• The unique constraint simply does not exist — uniqueness was assumed, never declared.
• Target column set differs from the index (missing the tenant column is the classic in multi-tenant schemas).
• The index is on an expression or partial, and the conflict target does not repeat it.

How to diagnose it

\d users   -- lists indexes and constraints with their exact definition

Compare the conflict target in the failing INSERT against the index definitions, checking columns, expressions and WHERE clauses literally.

How to fix it

-- Missing constraint → create it (in production: CONCURRENTLY)
CREATE UNIQUE INDEX CONCURRENTLY users_email_key ON users (email);

-- Expression index → the target must repeat it:
INSERT INTO users (email) VALUES ('[email protected]')
ON CONFLICT (lower(email)) DO NOTHING;

-- Partial index → the target must repeat the predicate:
INSERT INTO users (email) VALUES ('[email protected]')
ON CONFLICT (email) WHERE deleted_at IS NULL DO NOTHING;

Alternatively name the constraint directly — ON CONFLICT ON CONSTRAINT users_email_key — though the column form survives constraint renames better.