CYBERTEC Logo

PostgreSQL 15: Using MERGE in SQL

05.2022 / Category: / Tags: | |

It's been many years since the first ideas to implement the MERGE command surfaced on the PostgreSQL mailing list. Now this important feature has finally made it into PostgreSQL core, in PostgreSQL 15. To show people how this vital command works, I have decided to come up with a technical preview to introduce my readers to this wonderful command. It adds so much power to PostgreSQL.

MERGE: Preparing a sample table

As the name already suggests, MERGE can be used to merge lists and to combine them into one. It allows you to define the behavior, opens an easy way to import missing data and a lot more.

Let's prepare some data and try it out:

What we've produced is a list consisting of two columns. The second value is 10 times the value in the first column. This table will be our “target table” which is supposed to receive changes.

Using MERGE in PostgreSQL 15

Let's try to run MERGE on our data set and see what happens. Here's an example of a simple query:

The t_test table will be the target table and we merge “y”, which is the result of the USING clause, into it. The WHEN clauses simply define the rules applied during the MERGE operation. When we have a match, we want to overwrite the existing value. In case the value does not exist we want to run INSERT. There are two things worth mentioning here: If we look closely at the UPDATE statement, we see that the target table is not needed anymore. That's also true for the INSERT statement at the end - there is no need to define the table all over again.

The result is as expected:

What we see here is that all the odd numbers have been updated - the rest was unchanged. Missing rows were added.

However, there is more: We can also use DELETE inside of a MERGE statement:

In this case we have deleted all matching rows. The DELETE does not need any further arguments. It is totally clear which rows are affected and column information is not needed.

Finally …

MERGE is a valuable new feature in PostgreSQL 15. We've waited for many years and now it makes a lot of code much easier to deal with.
If you want to learn more about PostgreSQL and if you're interested in how PostgreSQL stores functions and procedures, check out our post about it.

0 0 votes
Article Rating
Subscribe
Notify of
guest
3 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Courage Dog
Courage Dog
1 year ago

Is the "WHEN NOT MATCHED BY SOURCE" clause is going to be supported? It's crucial for ETLs to mark records in data warehouse as deleted if they're hard deleted in source table. Thanks.

Christian R. Conrad
Christian R. Conrad
2 years ago

I'm not quite getting it... Shouldn't

generate_series(1, 16, 2)

Make a series 1, 3, 5, ... 11, 13, 15?

So why do your examples top out at id = 13; isn't a row with id = 15 missing?

Cybertec Schönig & Schönig Gmb

It is a great feature and I'm looking forward to try it out. Thanks for introducing it.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram