PostgreSQL 42P10: no unique constraint for ON CONFLICT

What it means

INSERT … ON CONFLICT (email) DO … tells PostgreSQL to watch for a clash on email. To spot a clash it needs a unique index (or a unique, primary key or exclusion constraint, which come with one) on exactly those columns. It looks for one when it plans the statement, and if none matches, it refuses to run, before inserting anything.

“Exactly” is strict:

The columns must be the same set: an index on (email, list) doesn’t serve ON CONFLICT (email), and one on email doesn’t serve ON CONFLICT (email, list). Their order doesn’t matter.
A partial unique index (… WHERE deleted_at IS NULL) only counts if the ON CONFLICT repeats its condition.
An expression index (lower(email)) only counts if the ON CONFLICT names the same expression.
An ordinary (non-unique) index never counts.

Common causes

The unique constraint was never created: the column is unique in your head, or in the ORM model, but not in the database.
The upsert names different columns from the unique key: one too many or one too few.
The unique index is partial (soft deletes, per-tenant uniqueness) and the ON CONFLICT doesn’t repeat its WHERE.
The unique index is on an expression such as lower(email), and the ON CONFLICT names the plain column.
A different database: the constraint exists in production but not in the test or new environment, because a migration didn’t run there.

How to fix it

See what unique indexes the table has

SELECT indexrelid::regclass AS index, pg_get_indexdef(indexrelid) AS definition
FROM pg_index
WHERE indrelid = 'subscribers'::regclass AND indisunique;

Your ON CONFLICT target must match one of these definitions: same columns, same expressions, and the same WHERE.

Add the unique constraint

ALTER TABLE subscribers ADD CONSTRAINT subscribers_email_list_key UNIQUE (email, list);

If existing rows already break the rule, this fails with duplicate key value (“could not create unique index”); remove the duplicates first. Adding the constraint builds an index under an ACCESS EXCLUSIVE lock, so reads and writes wait until it’s done. On a large, busy table, build the index with CREATE UNIQUE INDEX CONCURRENTLY and then attach it with ADD CONSTRAINT … UNIQUE USING INDEX; see adding a unique constraint.

Match a partial index

Repeat the index’s condition after the columns:

INSERT INTO subscribers (email, list) VALUES ('ada@example.com', 'news')
ON CONFLICT (email) WHERE deleted_at IS NULL DO NOTHING;

Match an expression index

Name the same expression:

INSERT INTO subscribers (email, list) VALUES ('Ada@example.com', 'news')
ON CONFLICT (lower(email), list) DO NOTHING;

ON CONFLICT ON CONSTRAINT <name> is another way to name the target, but it only accepts constraints. A unique index created with CREATE UNIQUE INDEX isn’t a constraint, so naming it fails with constraint "…" for table "…" does not exist.

Deferrable constraints can’t be used

A unique constraint declared DEFERRABLE can’t be an upsert target: ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters. Use a non-deferrable unique index for the upsert.

Reproduce it

On PostgreSQL 18.6, a table with only a primary key on id:

CREATE TABLE subscribers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL, list text NOT NULL, deleted_at timestamptz, name text);

INSERT INTO subscribers (email, list) VALUES ('ada@example.com', 'news')
ON CONFLICT (email) DO NOTHING;

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

With \set VERBOSITY verbose, psql shows the code: ERROR: 42P10: there is no unique or exclusion constraint matching the ON CONFLICT specification. In the same session:

After CREATE UNIQUE INDEX … ON subscribers (email) WHERE deleted_at IS NULL, ON CONFLICT (email) still failed; ON CONFLICT (email) WHERE deleted_at IS NULL worked.
After CREATE UNIQUE INDEX subscribers_lower_email ON subscribers (lower(email), list), ON CONFLICT (email, list) failed, ON CONFLICT (lower(email), list) worked, and ON CONFLICT ON CONSTRAINT subscribers_lower_email failed with constraint "subscribers_lower_email" for table "subscribers" does not exist.
After ADD CONSTRAINT subscribers_email_list_key UNIQUE (email, list), both ON CONFLICT (list, email) DO UPDATE … and ON CONFLICT ON CONSTRAINT subscribers_email_list_key worked.

PostgreSQL 14.24 gives the same message.

In Inlet

The structure editor shows a table’s indexes and constraints, adds a unique constraint with the DDL shown first, and warns when a change scans the table. When an upsert fails, Inlet shows the server’s error; with your own Anthropic API key, Ask Claude (Pro, ⌘L) can fix the statement, sending the schema, the SQL and the error, never rows.

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