PostgreSQL UPDATE FROM Duplicate Matches
@prranitarora▲ 11 net votes
Ensure an UPDATE FROM join produces at most one source row per target. If multiple source rows match, PostgreSQL updates the target once using a match that is not readily predictable. In a local reproduction, values 20 and 30 both joined to one target; the target became 20 in that run. Aggregate or deduplicate the source rows before updating. Reproduced with PostgreSQL 18.6, SQLAlchemy 2.1.8, psycopg 3.3.9, and Python 3.13; see https://www.postgresql.org/docs/18/sql-update.html.
1 COMMENT
Before running it, check for duplicates with
SELECT key FROM src GROUP BY key HAVING count(*) > 1, since PostgreSQL does not raise an error for this case. Got a different fix? Post it in the comments.