There's a great discussion here: https://wiki.postgresql.org/wiki/UPSERT#Syntax_discussion It basically boils down to that the different syntaxes mean slightly different things. The PG devs (IMO rightly) don't want to adopt an existing syntax that does something slightly different than what they are willing/able to provide, thus confusing users.

MERGE is actually a separate feature. We're still considering implementing it. But it's syntax is too cumbersome for many usages. There's also some unrelated complexity - so implementing merge support does get easier by the infrastructure in here, but a fair amount of work remains.

This seems MUCH better than MERGE for the common case.

I tried implementing some MERGE logic on MSSQL recently and at first it seemed great until I realised:

(a) maintaining/debugging a MERGE that uses most of its syntactical features is an absolute nightmare involving psychically debugging 20+ lines of opaque code - I ended up copying & pasting pieces of the statement into temporary tables/variables and confirming results that way, making me question the value of MERGE as the performance was similar to the original code, but less flexible (MERGE doesn't make available some of the values you need in some cases, so you need to do pre-mapping of data in those cases anyway, resulting in lots of similar code)

(b) it doesn't save you from needing to know and use the correct locks, something many people don't seem to realise!

I then discovered this page, listing all kinds of issues with the MERGE implementation (even on MS SQL 2014, years after MERGE had been introduced): http://www.mssqltips.com/sqlservertip/3074/use-caution-with-...

I realised that even if I followed my debugging approach in (a), one day I was going to run into a problem with MERGE that couldn't be replicated in decomposed statements because MERGE was doing 'something' else. And course, if you look at some of those issues, many of them suffer from the typical Microsoft "Won't fix and won't say why" attitude. In the end I decided to just keep the original/decomposed code, which was clearer and easier to work with.

MERGE is probably okay if you're staging data; I wouldn't use it for transactional processing. It is far too complicated. I really like that PG has focused on efficiently implementing the common use case and avoided the kitchen sink that is MERGE.

Please add MERGE support. I use this in Oracle fairly regularly, mostly for merging two datasets rather than single row upsert. It would be really helpful to have this in Postgres as well.

It's great that you guys implemented UPSERT but I hope MERGE support won't be considered as insignificant because of this feature.

I'm pretty sure it's not (going to be) considered insignificant.

Unfortunately that does not equate to resources (i.e. time by somebody sufficiently crazy^Wdetermined) for implementing it being available. A large part of postgres development is driven by individuals. Some of it on company time, but usually not most of it.

I'm a fan of MERGE in T-SQL. An "upsert" is just one use-case enabled by the statement. Granted for the relatively simple use-case of an "upsert", especially a single row, MERGE has incredibly cumbersome syntax.

But MERGE also, in my opinion, aligns better with set-based logic. I.e., I have two _sets_ of data that I want to merge together. In some cases I want need to INSERT rows into the target, in other cases I need to UPDATE, and in some cases I might DELETE. The <ouput_clause> in T-SQL is also quite useful.

The T-SQL design is far more flexible and expressive. However, I don't think I've ever used it beyond the simple case.

And on the flip side, it's a lot more complicated to write, and difficult even to remember the syntax (at least so far).

The T-SQL design also has numerous bugs, and allows for many nontrivial race conditions (which appears not to be a well-known fact).

http://www.mssqltips.com/sqlservertip/3074/use-caution-with-...

Postgres tries to avoid "gotchas" like this. If SQL Server fixes all the issues listed above by the time Postgres releases its first version of MERGE, but Postgres's version actually works the first time because they spent enough time up front addressing concurrency issues, then IMO Postgres will have made the right call.

(And yes, if Microsoft is unable to ever fix all these issues and Postgres never releases the feature, I would still stand by this statement. I want to be able to rely on every feature in my database).

I'm not very familiar with those specific features in other dbms, but in mysql's case "on duplicate" and "replace" are two very different thing. The first is an upsert, while the other drops the old row if it exists and always make a new one.

Both have different use cases.