ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification (SQLSTATE 42P10) means the columns in ON CONFLICT (...) are not covered by a unique constraint, a unique index or an exclusion constraint. PostgreSQL needs one to decide what counts as a conflict. Add a UNIQUE constraint on exactly those columns and the upsert works.

The fix is one statement, for example ALTER TABLE subscribers ADD CONSTRAINT subscribers_email_key UNIQUE (email). Then run the same upsert again, unchanged.

The error

INSERT INTO subscribers (email, name) VALUES ('maria.garcia@example.test', 'Maria G. Garcia')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
psql:/tmp/q1.sql:10: ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

Why it happens

An upsert is "insert, and if this row would duplicate an existing one, do something else instead". PostgreSQL does not compare rows on its own; it detects the duplicate through a unique or exclusion constraint, the same mechanism that raises a unique violation. The column list in ON CONFLICT (email) names which constraint to use. The table here had a primary key on id and nothing on email, so there was no constraint to match and the statement was rejected.

An ordinary index is not enough. With CREATE INDEX subscribers2_email_idx ON subscribers2 (email) in place, even ON CONFLICT (email) DO NOTHING failed with the same message: a non-unique index allows duplicates, so it cannot say two rows conflict.

The fix

ALTER TABLE subscribers ADD CONSTRAINT subscribers_email_key UNIQUE (email);
ALTER TABLE
INSERT INTO subscribers (email, name) VALUES ('maria.garcia@example.test', 'Maria G. Garcia')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
INSERT 0 1
SELECT id, email, name FROM subscribers;
 id |           email           |      name
----+---------------------------+-----------------
  1 | maria.garcia@example.test | Maria G. Garcia
(1 row)

The second insert updated the existing row instead of adding one: still one row, with the new name, and EXCLUDED.name is the value the insert proposed. Name the constraint yourself, as here, so it is easy to find later in \d subscribers and in migrations. From .NET, Npgsql reports this as a PostgresException with SqlState 42P10.

How it was reproduced

A postgres:18 container (PostgreSQL 18.4, Debian build 18.4-1.pgdg13+1) on Docker 29.9.2, with subscribers (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text NOT NULL, name text NOT NULL) and one fictional row. The upsert failed; after the UNIQUE constraint was added the same statement returned INSERT 0 1 and updated the row. A second table with only a plain index on email failed with the same message. A run with VERBOSITY=verbose printed ERROR: 42P10.

Frequently asked

How do I fix 'no unique or exclusion constraint matching the ON CONFLICT specification'?
Add a UNIQUE constraint on exactly the columns listed in ON CONFLICT (...), then run the upsert again. PostgreSQL detects conflicts only through such a constraint.
Is a normal index enough for ON CONFLICT in PostgreSQL?
No. With only a non-unique index on the column, both DO UPDATE and DO NOTHING fail with SQLSTATE 42P10. The index must be unique.
Does ON CONFLICT DO NOTHING need a unique constraint too?
Yes, when you name columns as in ON CONFLICT (email) DO NOTHING. The columns must match a unique or exclusion constraint, or the statement is rejected before it runs.

More decoded errors in the Fixes category.