cardinality_violation ON CONFLICT DO UPDATE command cannot affect row a second time — 21000

Symptoms

An INSERT … ON CONFLICT DO UPDATE tried to update the same existing row more than once within a single command, because the inserted rows contained duplicates on the conflict key. PostgreSQL raises SQLSTATE 21000 (cardinality_violation).

• The source rows include duplicates on the arbiter key.
• A single command may update a given target row only once.
• Common with batch upserts containing repeated keys.

What the server log shows

ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

Why PostgreSQL raises this — what the manual says

the INSERT reference (ON CONFLICT Clause):

“Rows proposed for insertion should not duplicate each other in terms of attributes constrained by an arbiter index or constraint.”

ON CONFLICT DO UPDATE may touch each conflicting target row at most once per command. If the batch contains duplicate keys that map to the same row, the second update is disallowed and PostgreSQL reports 21000.

Common causes

• An upsert batch with duplicate conflict-key values.
• Source data not deduplicated before the upsert.
• A join producing repeated keys feeding the INSERT.

How to fix it

1. Deduplicate the source rows on the conflict key before inserting (e.g. DISTINCT ON).
2. Pre-aggregate duplicates so each key appears once.
3. Split conflicting rows across separate commands if duplicates are intended.

Related & next steps

• SQLSTATE 21000 — Cardinality Violation (full reference)
• Message: no unique or exclusion constraint matching ON CONFLICT
• Glossary: Upsert

Reference: PostgreSQL 18 — INSERT.