The "WHEN NOT MATCHED THEN DO NOTHING" is very appealing, and we currently implement this with a transaction and a DELETE after the upsert because we want to do updates only, not unintentionally insert new rows.
Huh??? Why not just do an UPDATE then? After all, you seem to be saying "we want to do an upsert but without the insert part".
Edit: I'm also trying to understand the value of "WHEN NOT MATCHED THEN DO NOTHING". How is that any different that just leaving off the "WHEN NOT MATCHED" clause altogether?
(Agree that the “WHEN NOT MATCHED DO NOTHING” is effectively the default behavior.)
Use case is the Outbound Prober processes for https://heiioncall.com/ which can do thousands of HTTP requests per second, then aggregate all those row updates into a single query (or single transaction, which in practice is a single query).
We can do these batched updates very, very fast using INSERT … ON CONFLICT. No need to re-parse a differently shaped query, only a single index rebuild. Smooth :)