Postgres 15 will support MERGE

https://www.postgresql.org/docs/devel/sql-merge.html

53 5 4 years ago

sam_goody• 4 years ago

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?

zamalek• 4 years 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.

wswope• 4 years 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.

alvherre• 4 years 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.

jfbaro• 4 years ago

Many great things to be part of PG 15. Congratulations

Postgres 15 will support MERGE

Related Stories