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   -- elenca indici e vincoli con la loro definizione esatta

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

How to fix it

-- Manca il vincolo → crealo (in produzione: CONCURRENTLY)
CREATE UNIQUE INDEX CONCURRENTLY users_email_key ON users (email);

-- Indice su espressione → il target deve ripeterla:
INSERT INTO users (email) VALUES ('[email protected]')
ON CONFLICT (lower(email)) DO NOTHING;

-- Indice parziale → il target deve ripetere il predicato:
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.