[SigmundA]

Other systems like SQL server solve this with hints (HOLDLOCK) which is also discussed here that PG really needs hints.

So PG doesn't like hints and instead makes non standard syntax for upsert instead of standard merge syntax with a hint.

Now PG is adding merge anyway and probably could have just extended it with non standard syntax to get an atomic upsert instead it has two different approaches.

IMO it probably would have been better to go with the SQL standard MERGE with a non-standard hint syntax so its more readable between systems instead of adding non standard syntax then adding the standard much later.

Not sure if there is any movement on adding the upsert approach to the SQL standard instead.

[gfody]

to be fair sql server's holdlock is pretty specific to sql server, there's this note on pg's merge project page (https://wiki.postgresql.org/wiki/SQL_MERGE)

> PostgreSQL doesn't have a good way to lock access to a key value that doesn't exist yet--what other databases call key range locking (SQL Server for example). Improvements to the index implementation are needed to allow this feature.

as of 2017 at least the plan was to implement merge using the underlying insert on conflict mechanism and I think everyone agrees that the only reasonable expected behavior from merge is atomic upserts (and the pk violations from concurrent merges on sql server and oracle really should be recognized as bugs!) so maybe postgres merge will be the first non-buggy implementation w/o hints. reading the related threads exposes a surprising amount of complexity though so who knows.

[petergeoghegan]

> as of 2017 at least the plan was to implement merge using the underlying insert on conflict mechanism

That was something that was discussed at length at the time, but ultimately rejected. And so the implementation of MERGE in Postgres has essentially the same limitations as every other MERGE implementation that I'm familiar with.

Postgres clearly stresses the trade-off that MERGE makes in the docs, though. Which probably sets it apart.

[gfody]

afaik postgres still doesn't have a merge command (just tested on 14.2)?

[petergeoghegan]

It will be in Postgres 15, which will be out later in the year