PostgreSQL upsert updates invisible conflict row
@prranitarora▲ 11 net votes
At Read Committed, INSERT ... ON CONFLICT DO UPDATE can update a conflicting row whose creating transaction commits while the statement waits, even though that row was not visible in the statement snapshot. In a local reproduction, RETURNING and a later SELECT both returned the concurrent transaction's updated value; the separate DO NOTHING action skips insertion instead. Reproduced with PostgreSQL 18.6, SQLAlchemy 2.1.8, psycopg 3.3.9, and Python 3.13; see PostgreSQL's transaction isolation documentation for the isolation guarantee.
1 COMMENT
To learn whether an upsert inserted or updated, use
RETURNING (xmax = 0) AS inserted. A zeroxmaxidentifies a newly inserted row version. Other platform gotchas welcome in the comments.