SQLSTATE 42P10 ERROR Class 42: Syntax Error or Access Rule Violation

invalid_column_reference there is no unique or exclusion constraint matching the ON CONFLICT specification — 42P10

PostgreSQL error "there is no unique or exclusion constraint matching the ON CONFLICT specification" (SQLSTATE 42P10): what it means, common causes, and…

PG 12, 13, 14, 15, 16, 17, 18 Official docs
Last reviewed May 2025 Grounded in source

Diagnostic Queries

Symptoms

An INSERT … ON CONFLICT (…) named conflict-target columns that don’t correspond to any unique or exclusion constraint. PostgreSQL raises SQLSTATE 42P10 (invalid_column_reference).

  • The arbiter columns have no matching unique/exclusion constraint.
  • Common when a unique index/constraint is missing or differs.
  • ON CONFLICT needs an arbiter to detect conflicts.

What the server log shows

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

Why PostgreSQL raises this — what the manual says

the INSERT reference (ON CONFLICT Clause):

“In all cases, only NOT DEFERRABLE constraints and unique indexes are supported as arbiters.”

ON CONFLICT relies on an arbiter (a unique index or exclusion constraint) to detect duplicates. If no such constraint matches the specified columns, PostgreSQL cannot arbitrate conflicts and reports 42P10.

Common causes

  • No unique constraint/index on the conflict-target columns.
  • A partial/expression unique index that doesn’t match the target.
  • Wrong column list in the ON CONFLICT specification.

How to fix it

  1. Create a matching unique constraint/index on the target columns.
  2. Match the ON CONFLICT column list exactly to an existing unique index.
  3. Use ON CONFLICT ON CONSTRAINT constraint_name to name it directly.

Related & next steps

Reference: PostgreSQL 18 — INSERT.

Was this helpful?