no unique or exclusion constraint matching ON CONFLICT

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification (SQLSTATE 42P10) means the column list in ON CONFLICT (…) does not match any unique index on the table. PostgreSQL does not look for “an index that contains these columns” but for a valid unique index whose key columns and expressions are exactly the ones you listed — in any order; INCLUDE columns do not count — and whose WHERE clause, if it has one, follows from the WHERE you wrote. In a multi-tenant schema the usual cause is ON CONFLICT (email) against a constraint on (tenant_id, email); the second is a partial unique index for soft deletes, which needs its WHERE repeated in the statement.

We measured every case on PostgreSQL 17.11, plus SQLAlchemy 2.1.6 and Django 6.1.2 on top. The error comes without a DETAIL or HINT, even in verbose mode: it does not say which indexes exist.

Which ON CONFLICT target matches which index?

The partial index is the one that catches multi-tenant applications: soft delete is common, the index is the right way to keep addresses unique among live rows (measured in unique per tenant with soft delete), and the upsert written for the old full constraint stops working the day the index changes. Because a partial unique index is never a constraint — add constraint … unique using index refused it with is a partial index — the ON CONSTRAINT form cannot name it; the only way is to repeat the predicate.

A target also only makes the matching indexes arbiters. With unique (tenant_id, email) and unique (tenant_id, ext), on conflict (tenant_id, email) do nothing on a row that collided on ext raised duplicate key value violates unique constraint "u_tenant_id_ext_key"; without a target, the same row gave INSERT 0 0.

Which constraints can never be an ON CONFLICT arbiter?

Deferrable constraints. unique (tenant_id, email) deferrable initially deferred gave a different message: 55000: ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters — with a column list, with on constraint, and even with no target at all. deferrable initially immediate gave it too, on an empty table. Worse: one deferrable constraint anywhere on the table broke every on conflict do nothing without a target — which is exactly what Django’s bulk_create(…, ignore_conflicts=True) sends — with 55000 for a row that conflicted with nothing, while a row that did collide on a non-deferrable constraint went through as INSERT 0 0. The error depended on the data. A target that names a non-deferrable index still worked.

Exclusion constraints, for updates. On a booking table with exclude using gist (room with =, during with &&), on conflict do nothing without a target worked, and so did on constraint. A column list — even exactly (room, during) — gave 42P10, do update with on constraint gave 42809: ON CONFLICT DO UPDATE not supported with exclusion constraints, and do update without a target 42601: … requires inference specification or constraint name.

A unique index on one partition only. On a table partitioned by tenant_id, a unique index on the parent over (tenant_id, email) worked as a target. A unique index on (email) alone could not be created on the parent (0A000: it must contain the partition key). With a unique index on one partition only, an insert through the parent with a target got 42P10, while do nothing without a target did catch the duplicate through the partition’s index, and the same target worked on the partition itself. The diagnosis query below then shows nothing for the parent; run it per partition.

What do NULLs do to ON CONFLICT?

With an ordinary unique index on a nullable column, the target was accepted, but two rows with NULL never conflicted: both inserts returned INSERT 0 1, also with do update. So an upsert keyed on a column that can be NULL inserts a new row every time for those rows. Declared unique nulls not distinct (PostgreSQL 15 and later), the second insert returned INSERT 0 0, and do update updated the existing row. When a plain insert does raise the duplicate instead, that error is measured in duplicate key value violates unique constraint.

What do SQLAlchemy and Django send?

SQLAlchemy’s insert(Customer).on_conflict_do_nothing(index_elements=['email']) sent INSERT INTO … ON CONFLICT (email) DO NOTHING and raised sqlalchemy.exc.ProgrammingError around psycopg2.errors.InvalidColumnReference — the class name for 42P10. With index_elements=['tenant_id', 'email'] and index_where=Customer.deleted_at.is_(None) it matched the partial index. Passing the index name as constraint= produced ON CONFLICT ON CONSTRAINT … and UndefinedObject (42704), for the same reason as in SQL.

Django’s bulk_create(…, update_conflicts=True, unique_fields=['email'], update_fields=['name']) sent ON CONFLICT("email") DO UPDATE SET "name" = EXCLUDED."name" and raised django.db.utils.ProgrammingError, caused by InvalidColumnReference. unique_fields=['tenant', 'email'] worked, in either order — for a plain constraint. With a conditional UniqueConstraint(…, condition=Q(deleted_at__isnull=True)), the soft-delete case, update_conflicts cannot work: Django has no way to send the predicate, and the same ON CONFLICT("tenant_id", "email") gave 42P10. ignore_conflicts=True sends ON CONFLICT DO NOTHING without a target and did work there — unless the table also has a deferrable constraint, see above.

How do you find which target fits?

This lists every unique index and exclusion constraint on a table, with what a matching target has to contain:

-- psql -v tbl=customers -f arbiters.sql   (PostgreSQL 15+; on older versions drop nulls_not_distinct)
select i.indexrelid::regclass                          as index_name,
       coalesce(c.contype::text, 'index only')         as kind,
       (select string_agg(coalesce(a.attname, '<expression>'), ', ' order by k.ord)
          from unnest(i.indkey::int2[]) with ordinality k(attnum, ord)
          left join pg_attribute a
                 on a.attrelid = i.indrelid and a.attnum = k.attnum and k.attnum > 0
         where k.ord <= i.indnkeyatts)                 as key_columns,
       pg_get_expr(i.indexprs, i.indrelid)             as expressions,
       pg_get_expr(i.indpred, i.indrelid)              as where_clause,
       coalesce(c.condeferrable, false)                as deferrable,
       i.indnullsnotdistinct                           as nulls_not_distinct,
       i.indisvalid                                    as valid
from pg_index i
left join pg_constraint c
       on c.conindid = i.indexrelid and c.conrelid = i.indrelid and c.contype in ('p', 'u', 'x')
where i.indrelid = :'tbl'::regclass
  and (i.indisunique or c.contype = 'x')
order by 1;

On the soft-delete table:

         index_name         |    kind    |   key_columns    | expressions |     where_clause     | deferrable | nulls_not_distinct | valid
----------------------------+------------+------------------+-------------+----------------------+------------+--------------------+-------
 m3.customers_pkey          | p          | id               |             |                      | f          | f                  | t
 m3.customers_live_email_uq | index only | tenant_id, email |             | (deleted_at IS NULL) | f          | f                  | t

index only with a where_clause means: list tenant_id, email and repeat where deleted_at is null; on constraint will not work. A row with <expression> in key_columns means: copy the expression from the expressions column. kind = x is an exclusion constraint: its key_columns are not a valid target — use on constraint or no target, with do nothing only. deferrable = t is an arbiter ON CONFLICT refuses, and valid = f an index left by a failed concurrent build: drop it and build it again.

What we did not measure

Prisma and other ORMs. MERGE. Upserts on views.

All measurements are our own, on PostgreSQL 17.11 in containers, with SQLAlchemy 2.1.6, psycopg2 2.9.13 and Django 6.1.2, on 22 October 2026.