5 ms·
Upcoming feature in PostgreSQL 15 – MERGE statement
- gigatexal 5y agoIt just shows the command. No talk about query plan performance or advantages over multiple updates and or deletes (which should be obvious I guess since this is one statement vs two or more). Otherwise it’s a pretty lackluster blog post.
- iknownothow 5y agoI'm not denying the importance of performance and query planning here, but merging two statements into one is what I as a (specific kind of) data engineer care about more. Letting postgres take care of consistency while keeping the SQL readable is just pure value addition.
- philliphaydon 5y agoAh I’m scared. This feature is in SQL Server and it causes so many issues.
- zulu-inuoe 5y agoCould you share some of the problems it causes? SQL Neophyte here
- AdrianB1 5y agoI never read the details, but I know that several SQL MVPs consider MERGE in SQL just a feature to check a box in the product presentation brochure, not to be used in real life. Brent Ozar talked about this a few times, foe example.
- d_watt 5y agoDoes this fill the same role as "Insert - On Conflict Do Update," but it doesn't require unique indexes?
- philliphaydon 5y agohttps://www.postgresql.org/message-id/CAM3SWZRP0c3g6+aJ=YYDGYAcTZg0xA8-1_FCVo5Xm7hrEL34kw@mail.gmail.com https://www.postgresql.org/message-id/CAM3SWZRP0c3g6+aJ=YYDG... upsert is built on top of insert while merge allows you specify multiple conditions and includes the ability to perform a delete. i.e if you have a blog post with a list of tags, you could say: - if submitted tag is not found > insert it - if submitted tag is found > do nothing - if tag exists that wasn't submitted > delete it It can be quite powerful but it can get difficult to read also. I just hate using it in SQL Server because every time someone adds it, a year later its the code causing deadlocks.
- d_watt 5y agoThanks for the link. Now I need to dig deeper on all the concerns brought up there around non-atomicity and table level locking! Seems like it could be useful specifically for bulk uploads.
- WorldMaker 5y agoOne of MERGEs goals is to be the ANSI standard for "Upsert" queries, yes, ("On Conflict Do Update" is not standardized) but it's also a more powerful tool than that. It's almost a full bulk ETL tool allowing for some complex multi-table join-based bulk INSERT/UPDATE/DELETE (all at once). (With great power comes great responsibility; as others point out MERGE in SQL Server can get you big table deadlocks if you aren't careful, but on the flipside in my experience there were a lot of very hairy stored procedures I was able to rewrite as single much easier to read MERGE statements that had far better query plans and were much faster overall.)
- devit 5y agoA mere 18 years after it was published in the SQL:2003 standard. I guess better late than never.
- exabrial 5y agoI was going to ask: Is the behavior defined by ANSI SQL? Because if it is not, it's worthless; so this is great news! The only constant in computing is any feature of a language or database that doesn't have a formal definition is guaranteed to break later.
- merb 5y agothis looks sick and probably makes materialized views (without updating everything at once) a little bit more easier.
- gavinray 5y agoAnother neat Postgres 15 feature: SQL 2016 Standard JSON operator support https://postgrespro.com/blog/pgsql/5969433 https://postgrespro.com/blog/pgsql/5969433