Introducing our new eBook: "Best Practices for Optimizing Postgres Query Performance"Download Now

U125: No unique or exclusion constraint matching the ON CONFLICT specification

Category: Application / User Errors
SQLSTATE: 42P10 (Class 42 — Syntax Error or Access Rule Violation: invalid_column_reference)
Urgency: low

Example Postgres Log Output:

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
STATEMENT: INSERT INTO x (y, z) VALUES ('a', 1) ON CONFLICT (y) DO UPDATE SET z = EXCLUDED.z

Explanation:

Your INSERT INTO ... ON CONFLICT statement is missing the unique or exclusion constraint thats required to determine where a row is equivalent (i.e. conflicting) with an existing row in the table.

This may be due to the automatic inference not finding your constraint, or the more likely case that you have forgotten to specify one or more columns that identify the constraint.

You can explicitly specify the constraint like this:

INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')
  ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING;

Recommended Action:

Adjust your application and rewrite your query to correctly reference the exclusion/unique constraint required by ON CONFLICT.

In some cases you may also need to create the missing UNIQUE index or exclusion constraint.

Learn More:


Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →