U126: ON CONFLICT DO UPDATE command cannot affect row a second time

Category: Application / User Errors
SQLSTATE: 21000 (Class 21 — Cardinality Violation: cardinality_violation)
Urgency: low

Example Postgres Log Output:

ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
STATEMENT: INSERT INTO x (y, z) VALUES ('a', 1), ('a', 2) ON CONFLICT (y) DO UPDATE SET z = EXCLUDED.z
HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

Explanation:

The ON CONFLICT statement inserts the same row twice, as identified by the values in the constrained columns (i.e. those supposed to differentiate rows).

This usually happens if you don't de-duplicate your input before passing it to Postgres, as then ON CONFLICT would operate twice on the same set of values for the constraint clause, which is currently not supported.

Recommended Action:

You need to adjust your application's INSERT logic to deduplicate rows before passing them to PostgreSQL.

Learn More:


Download Free eBook: The Top 6 Postgres Log Events
Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →