For those who may be interested: I was curious about the difference between MERGE [1] and UPSERT, and why database engines seem to support a custom (nonstandard

For those who may be interested: I was curious about the difference between MERGE [1] and UPSERT, and why database engines seem to support a custom (nonstandard) UPSERT instead of the MERGE introduced in standard SQL in 2003, so I did some web research and came across a reasonable-sounding answer in a comment on the following article. https://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ https://www.depesz.com/2012/06/10/why-is-upsert-so-complicat... > As noted, MERGE is complicated and slow. If two different database servers implement it, then it is going to be implemented with different schematics [sic]. Oracle and MySQL already have different implementation’s, and to nobody’s surprise, MySQL’s implementation allows for risky behavior that does not protect the data. PostgreSQL users expect that any data put into the database will reliably come out of the database. So MERGE is slow and inconsistently implemented. The correct thing to do is make the application aware of if a record is new or existing. Any application making use of MERGE should open a bug to replace it with more predictable and faster logic using INSERT and UPDATE. > If an application does use MERGE, it has to account for implementation specific behavior and not will not be portable, in a safe way, to other database servers. So it is not suitable for ORM’s or database independent applications. > So what is a legitimate use case for MERGE where not knowing if a record is new or existing is not possible prior to the transaction? They only thing that I can think of is one-off scripts that do not have any concurrency. But if you design for that, then some misguided ORM for web applications is going to use your MERGE function and users are going to wonder about what happened to their data when concurrency concerns were ignored or not understood. (I believe this comment is by Michael McLaughlin, the author of books on Oracle PL/SQL programming.) Does anyone happen to know if this is still accurate today, or whether there's been any attempt to incorporate UPSERT support into standard SQL? I could imagine the SQL standard maintainers not wanting to offer UPSERT if MERGE is already a superset of its functionality; but a case could be made that, since MERGE's complexity has apparently discouraged adoption by implementations and users, it may be sensible to support UPSERT nonetheless. Is this a reasonable way to think about it? I don't mean to imply that standardizing UPSERT into SQL is an especially important concern for anyone; this is merely a trail of thought that originated from wondering about why the UPSERT statement is nonstandard. [1] https://en.wikipedia.org/wiki/Merge_(SQL) https://en.wikipedia.org/wiki/Merge_(SQL)