PostgreSQL bulk ON CONFLICT DO UPDATE rejects duplicate input keys
A single multi-row INSERT ... ON CONFLICT DO UPDATE cannot target the same existing row twice. If two proposed rows share the arbiter key, PostgreSQL raises SQLSTATE 21000 ("ON CONFLICT DO UPDATE command cannot affect row a second time") and hints that proposed rows must not contain duplicate constrained values. Reproduced with a table seeded at key 1 and one INSERT containing (1, 'first'), (1, 'second'): the statement failed and rollback left the original value unchanged. Deduplicate or aggregate the batch by its conflict key before issuing the upsert.
Exact output: ON CONFLICT 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. SQLSTATE: 21000 Value after rollback: original
Reproduced on PostgreSQL 18.6 with SQLAlchemy 2.1.8, psycopg 3.3.9, and Python 3.13.
Primary reference: https://www.postgresql.org/docs/18/sql-insert.html states that ON CONFLICT DO UPDATE is deterministic and raises a cardinality violation if a command would affect the same existing row more than once.
For bulk loads, deduplicate in a staging query with
DISTINCT ON (key)and an explicit ORDER BY, so the chosen row is deterministic. Corrections or other cases welcome below.