[mullsork]

SQL MERGE looks great! I hope I remember it when the time comes, instead of writing 3 separate queries.

edit: Postgres docs on MERGE: https://www.postgresql.org/docs/15/sql-merge.html

[CWuestefeld]

Hmmm. The doc kinda suggests that this might be more efficient than doing it with separate commands:

"First, the MERGE command performs a join from data_source to target_table_name producing zero or more candidate change rows. For each candidate change row, the status of MATCHED or NOT MATCHED is set just once, after which WHEN clauses are evaluated in the order specified. For each candidate change row, the first clause to evaluate as true is executed."

Anybody know more about this?

From lots of experience with SQL Server, I know that over there, MERGE is not more efficient, it's just syntactic sugar - and in fact it's buggy syntactic sugar, as there are some conditions where it doesn't handle concurrency properly.

[jabiko]

Section 13.2. Transaction Isolation" has some additional information regarding the behavior of MERGE. Just CTRL-F and search for "MERGE" on https://www.postgresql.org/docs/15/transaction-iso.html#XACT...

[chrisjc]

I'm actually surprised to hear that MERGE is only now available on Postgres.

I'm now interesting in hearing about other standard (what I have come to expect as standard) SQL that's not or only now available on Postgres?

[systems]

Well MS SQL Merge statement is not very good, and I personally avoid it, and most places I worked in recommend to avoid it, except in the simplest scenarios

From the docs "At scale, MERGE may introduce complicated concurrency issues or require advanced troubleshooting. As such, plan to thoroughly test any MERGE statement before deploying to production."

I dont know if its better in PQSQL , but they took their time, so maybe it is

[trollied]

It kind of did support it before. You could do an INSERT … ON CONFLICT ( keys here) DO UPDATE update query here

[jeltz]

The reason is that PostgreSQL has INSERT ... ON CONFLICT which is usually what you want, especially since it handles concurrency in the way you usually want. MERGE has more capabilities but not enough of them to make such a complex feature prioritized.

[ptrwis]

At least for some cases, there was a workaround by using INSERT ... ON CONFLICT

[singingfish]

I'm currently neck deep in a decent sized oracle to postgres project, and MERGE INTO saved me many many hours