Comment by kbolino

I don't want partial updates, I want full, conflict-free upserts.

At read committed (default) isolation level, INSERT ... ON CONFLICT handles concurrent, conflicting inserts just fine, while MERGE ... WHEN NOT MATCHED (e.g.) does not. This is surprising behavior from the syntax alone, one would assume the two statements, when written with the same intent, would have the same behavior. Of course, this difference is documented, see the last paragraph of the Notes section on MERGE: https://www.postgresql.org/docs/18/sql-merge.html#id-1.9.3.1...

I don't know this for sure, but I believe that the effect of raising the transaction isolation level will just be to turn the constraint violation into a serialization error. That's not any easier to handle gracefully.