A preview of PostgreSQL 9.5

The way I might now explain SQL MERGE is by making a comparison to GROUPING SETS in PostgreSQL.

GROUPING SETS are very useful, but do not allow you to do anything that was not already fundamentally possible with a single SQL statement before 9.5 (you could accomplish something equivalent with a bunch of UNION ALLs in earlier versions). GROUPING SETS are very useful because the syntax is a more direct and natural way of expressing a common requirement, and because internally, the output from one grouping set can be reused for another, making performance far superior. But the GROUPING SETS feature plays by the same rules as conventional queries, and comes from the SQL standard, and so is not a controversial idea.

MERGE is similar. It's nice to be able to express a common requirement (reconciling two tables) more directly, and it's really nice to be able to have that reconciliation driven by a conventional join, with all of the attendant performance benefits. But that does not (and, for all practical purposes, cannot) have anything to do with *guarantees* about outcome. You can accomplish the same thing with data-modify WITH clauses, even if that is ugly and potentially slow.

How does this relate to UPSERT? MERGE (and the data-modifying WITH approach) have certain problems that are just inherent to how they need to work. "Is some row version in the target not visible to your MVCC snapshot? Guess that means that you'll get a duplicate violation when the WHEN NOT MATCHED THEN INSERT handler is taken." (and so on)

I wanted to give PostgreSQL users an easy way to express a "simple" requirement, giving them a guarantee about an *outcome*, which is what makes UPSERT easy to use correctly. That necessarily implies that if you pay close attention to what I came up with, you can see that to a limited extent it takes liberties with MVCC semantics (in READ COMMITTED mode). We don't think that's a problem now, but issues like that certainly made for a difficult and protracted discussion. SQL MERGE definitely has some upsides for bulk loading and so on, but I think it would have been a far less worthwhile (and far easier) project.