Postgres 15 will support MERGE (opens in new tab)

(postgresql.org)

53 pointsi_have_to_speak3y ago5 comments

5 comments

While Postgres docs are great (they usually are), I would love some more backgrond.

This looks it takes an entire column and upserts all of its rows into another column, on the same or another table.

Is that correct? Is this part of the SQL standard? Is this the same as MERGE in SQL Server and Oracle (which has no counterpart in MySQL)?

IIRC upsert in PG is different than in Mysql, MS or Oracle; is that the same here?

Are there any good resources for this?

alvherre3y ago

Yes, MERGE is specified by the SQL standard. Our implementation (Postgres) aims to conform to the standard, so it should roughly match what both Oracle and SQL Server offer.

(Microsoft seems to have added a BY TARGET/BY SOURCE specification to the WHEN NOT MATCHED clause, which is not in the standard. Oracle seems to do DELETE in a different, nonstandard way.)

"Upsert" (which is called INSERT ON CONFLICT UPDATE in Postgres) is a different animal. It is not part of the SQL standard, which is why every database system does its own stuff.

wswope3y ago

IIUC, it’s an improvement over Postgres’ existing upsert functionality, which is centered around “INSERT INTO… ON CONFLICT UPDATE”. Instead of having to choose which action to take based on constraint conflicts, you can specify insert vs. update based on any arbitrary expression now, which formerly would’ve required some pgplsql or the like to write an IF… ELSE… branch.

zamalek3y ago

The grammar looks virtually identical to MSSQL, so it probably aims for the same goals.

Merge acts on multiple rows and columns at the same time.

jfbaro3y ago

Many great things to be part of PG 15. Congratulations

j / k navigate · click thread line to collapse