Upcoming feature in PostgreSQL 15 – MERGE statement | Hacker News Reader

Upcoming feature in PostgreSQL 15 – MERGE statement

highgo.ca

38 pointsTomte13 comments

A mere 18 years after it was published in the SQL:2003 standard.

I guess better late than never.

I 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.

It 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.

I'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.

Ah I’m scared. This feature is in SQL Server and it causes so many issues.

Could you share some of the problems it causes? SQL Neophyte here

I 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.

Another neat Postgres 15 feature: SQL 2016 Standard JSON operator support

https://postgrespro.com/blog/pgsql/5969433

this looks sick and probably makes materialized views (without updating everything at once) a little bit more easier.

Does this fill the same role as "Insert - On Conflict Do Update," but it doesn't require unique indexes?

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.

Thanks 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.

One 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.)

Reply on news.ycombinator.com