2 ms·
It's certainly not jus sql server. The implementor of INSERT…ON CONFLICT UPDATE in Postgres considered and rejected MERGE[0] on grounds that it's simply not spe
by masklinn 6y ago
It's certainly not jus sql server. The implementor of INSERT…ON CONFLICT UPDATE in Postgres considered and rejected MERGE[0] on grounds that it's simply not specified to do what people generally want
> MERGE, as implemented within other major systems and as described by the SQL standard does not require an atomic insert-or-update for even the simplest possible case of OLTP style insert-or-update.
> SQL MERGE as implemented within other systems doesn't require that a unique index be defined on the column or columns of the MERGE statement's outer join. I am not enthusiastic about the idea of falling back to table-level locking when an appropriate index isn't available. That could be a foot-gun for the large majority of users that just want to solve their basic insert-or-update problem.
A MERGE patch was submitted for pg11, it got bumped multiple time and returned with comments early 2019 with no visible activity since.
[0] https://www.postgresql.org/message-id/CAM3SWZRP0c3g6+aJ=YYDGYAcTZg0xA8-1_FCVo5Xm7hrEL34kw@mail.gmail.com https://www.postgresql.org/message-id/CAM3SWZRP0c3g6+aJ=YYDG...
- gfody 6y agothis has nothing to do with SQL Server. on SQL Server MERGE is atomic, if the source has no indexes it's probably a temp table, of course evaluating "not matched" requires joining the source to the target, on SQL Server you should be thinking and working in sets and avoiding single row (RBAR) whenever possible. if you're doing highly concurrent single-row upserts you're already SQL Servering wrong and I'll concede that's a case where you probably need a bunch of lock hints (regardless if it's a merge statement or an update followed by an insert)