Postgres 15 will support MERGE

#1

Post by i_have_to_speak » Tue, Mar 29, 2022, 9:14 AM UTC

Postgres 15 will support MERGE
postgresql.org

Re: Postgres 15 will support MERGE

#2

Post by sam_goody » Tue, Mar 29, 2022, 1:11 PM UTC

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?

Re: Postgres 15 will support MERGE

#3

Post by jfbaro » Tue, Mar 29, 2022, 1:13 PM UTC

Many great things to be part of PG 15. Congratulations

Re: Postgres 15 will support MERGE

#4

Post by zamalek » Tue, Mar 29, 2022, 1:36 PM UTC

sam_goody wrote: post #2

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…

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.

Re: Postgres 15 will support MERGE

#5

Post by wswope » Tue, Mar 29, 2022, 3:17 PM UTC

sam_goody wrote: post #2

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…

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.

Re: Postgres 15 will support MERGE

#6

Post by alvherre » Wed, Mar 30, 2022, 5:44 PM UTC

sam_goody wrote: post #2

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…

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.