In most good databases you can compose an upsert transactionally from a set of independent operations. The problem is that this is slow because you are plumbing the database engine multiple times for an operation that could be executed in one shot in principle. A native upsert implementation provides a fast, single operation. In the case of PostgreSQL though, they had to support this in the context of high-performance, high-concurrency, and correctness which makes it tricky because it is relatively complex to implement under those constraints.
<literal>ON CONFLICT DO UPDATE</literal> guarantees an atomic
<command>INSERT</command> or <command>UPDATE</command> outcome - provided
there is no independent error, one of those two outcomes is guaranteed,
even under high concurrency.
I don't know about Oracle or MySQL, but the SQL Server implementation of MERGE requires you to carefully consider which locks to use and it's very easy to shoot yourself in the foot with.[1] http://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdif...
http://www.mssqltips.com/sqlservertip/3074/use-caution-with-...
And also this:
http://www.depesz.com/2012/06/10/why-is-upsert-so-complicate...
I haven't tried, but Postgres always uses values in sequences in similar cases. The good news is that there are so many values in a 64 bit sequence that this really shouldn't matter.
I much prefer some values in sequences to be not used over even the tiniest possibility of a value being used twice accidentally.