[jeltz]

The new PostgreSQL syntax is more convenient to use in the UPSERT use case while the MERGE syntax is more convenient to use when doing complicated operations on many rows of data (for example when merging one table into another, with a non-tricial merge logic).

The reason PostgreSQL went with this syntax is that the goal was to create a good UPSERT and getting the concurrency considerations right with MERGE is hard (I am not sure of the current status, but when MERGE was new in MS SQL it was unusable for UPSERT) and even when you have done that it would still be cumbersome to use for UPSERT.

EDIT: The huge difference is that PostgreSQL's UPSERT always requires a unique constraint (or PK) to work, while MERGE does not. PostgreSQL relies on the unique constraint to implement the UPSERT logic.

[dsp1234]

I am not sure of the current status, but when MERGE was new in MS SQL it was unusable for UPSERT

I've used MERGE as an UPSERT using MATCHED/NOT MATCHED and SERIALIZABLE/HOLDLOCK since it was introduced in mssql 2008. It was one of the first features I upgraded my code to use, and it worked out of the box with no issues.

[jeltz]

See this blog post for what I am talking about: https://www.mssqltips.com/sqlservertip/3074/use-caution-with...

If PostgreSQL had gone the same route as MS SQL I would have expected a similar set of bugs. I suspect all of this have been fixed by now, but I do not follow MS SQL.