3 ms·
Does this fill the same role as "Insert - On Conflict Do Update," but it doesn't require unique indexes?
by d_watt 4y ago
Does this fill the same role as "Insert - On Conflict Do Update," but it doesn't require unique indexes?
- philliphaydon 4y 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 4y 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 4y 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.)