4 ms·

how does postgresql upsert compare to ms sql's merge statement i want to look deeper into this, but didnt have the time but from the little i read, seems ms sq

by systems 11y ago

how does postgresql upsert compare to ms sql's merge statement i want to look deeper into this, but didnt have the time but from the little i read, seems ms sql merge is more powerful

manigandham 11y ago
Lots of databases have MERGE but it's different from the typical UPDATE OR INSERT logic in terms of use cases, table requirements and concurrency control. Here's a great post from Postgres team showing why they didn't just implement merge themselves: http://www.postgresql.org/message-id/CAM3SWZRP0c3g6+aJ=YYDGYAcTZg0xA8-1_FCVo5Xm7hrEL34kw@mail.gmail.com http://www.postgresql.org/message-id/CAM3SWZRP0c3g6+aJ=YYDGY...
jeltz 11y ago
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 11y ago
  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 11y ago
    See this blog post for what I am talking about: https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ 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.

by systems 11y ago

how does postgresql upsert compare to ms sql's merge statement i want to look deeper into this, but didnt have the time but from the little i read, seems ms sql merge is more powerful

manigandham 11y ago

Lots of databases have MERGE but it's different from the typical UPDATE OR INSERT logic in terms of use cases, table requirements and concurrency control. Here's a great post from Postgres team showing why they didn't just implement merge themselves: http://www.postgresql.org/message-id/CAM3SWZRP0c3g6+aJ=YYDGYAcTZg0xA8-1_FCVo5Xm7hrEL34kw@mail.gmail.com http://www.postgresql.org/message-id/CAM3SWZRP0c3g6+aJ=YYDGY...

jeltz 11y ago

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 11y ago
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 11y ago
  See this blog post for what I am talking about: https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ 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.