MERGE was introduced to PostgreSQL in version 15 and it will have a major impact on the way you write queries. This will definitely change the way you can handle transaction logs and other similar updates.
Imagine that you have an existing table filled with useful data, say an inventory of goods your company sells, and from time to time you get data sets with either new products that need to be entered or inventory lists that may, or may not, have a new quantity of goods on hand. This is normally the type of work you would have to do in an application. The problem with using an application to do this is that the data has to be shifted back and forth, maybe several times, from the database to the application and that is a lot of overhead (pronounced, in this case, as ‘time’). So now all this work can be done with one command on the database server.
Both SQL Server and Oracle have this operator, it has been part of the SQL standard for a while, and PostgreSQL recently added it with the release of version 15. You get the power to INSERT, UPDATE, and DELETE all in one statement. While the syntax looks a little complex it is way beyond a simple ‘upsert’ as you will soon see.
Terminology
We will have two tables to process with MERGE. The table with the updates is called the source table and the main repository of the data is called the target table. The contents of the source table are compared to the target table and actions are made on the target table. Rows are considered to match or not match between the two tables and actions are taken accordingly on these matches.
Example of how it works
Let’s start with two tables and one row of data.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | test=# create table a (id int, x int, status char(10)); CREATE TABLE test=# create table b (id int, x int, status char(10)); CREATE TABLE test=# insert into a (id,x,status) values (1,1,'From a'); INSERT 0 1 test=# select * from a;select * from b; id | x | status ----+---+------------ 1 | 1 | From a (1 row) id | x | status ----+---+-------- (0 rows) |
Now we can use MERGE in a query. Remember as you look at this query below that table a has one row of data and table b has no rows of data. In this query, we INSERT records not on the target table and UPDATE those that are.
1 2 3 4 5 | MERGE into b using a on a.id = b.id when matched then update set x = b.x + 1 when not matched then insert (id,x,status) values (a.id,a.x,a.status); |
The query instructs the server to match the two tables on the id columns. If there are rows that match then column x in table b is incremented by one. But if there are no matches (there will not be a match as table b is empty) then the contents of the row from table a are copied to table b.
If we look at the contents of both tables explicitly, we see that the two tables are now identical.
1 2 3 4 5 6 7 8 9 10 11 | test=# select * from a; select * from b; id | x | status ----+---+------------ 1 | 1 | From a (1 row) id | x | status ----+---+------------ 1 | 1 | From a (1 row) |
If we use EXPLAIN on the query we get the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 | test-# update set x = b.x + 1 test-# when not matched then insert (id,x,status) values (a.id,a.x,a.status); QUERY PLAN ---------------------------------------------------------------- Merge on b (cost=0.00..1.02 rows=0 width=0) -> Nested Loop Left Join (cost=0.00..1.02 rows=1 width=25) Join Filter: (a.id = b.id) -> Seq Scan on a (cost=0.00..1.01 rows=1 width=19) -> Seq Scan on b (cost=0.00..0.00 rows=1 width=10) (5 rows) test=# |
And note the update line needs to be update set x = b.x +1 not update set b.x =b.x + 1 as it will trigger a syntax error. Plus you can not update table a – only the target table.
Run MERGE again
Now run the MERGE query a second time and look at the contents of the two tables. This time the matched logic was executed and column x had its value increased from 1 to 2.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | test=# select * from a; select * from b; id | x | status ----+---+------------ 1 | 1 | From a (1 row) id | x | status ----+---+------------ 1 | 2 | From a (1 row) test=# |
And yes, every time we rerun the MERGE query column x will be incremented.
DELETEs too!
DELETE will remove rows from the target table b if it finds a row that matches. Let’s change the query so that instead of bumping the value of a column we just delete the matching row in the target table.
1 2 3 4 5 6 7 8 | test=# merge into b using a on a.id = b.id test-# when matched then delete test-# when not matched then insert (id,x,status) values (a.id,a.x,a.status); MERGE 1 test=# select * from b; id | x | status ----+---+-------- (0 rows) |
DOING nothing
One of our options is to DO NOTHING. Yup, we find that the record is already in the source table and just skip over it. Currently, table b has nothing in it after the DELETE example. So run the following query to populate the table and then again.
1 2 3 4 5 6 7 | test=# MERGE into b using a on a.id = b.id test-# when matched then test-# DO NOTHING test-# when not matched then test-# insert (id,x,status) values (a.id,a.x,a.status); MERGE 1 test=# |
COMPLEXITY too!
You can make MERGE as complex as you need. In the following, you will see that we can act on different values of columns of the target table. We start by truncating the target table and then run the MERGE query to the first popular table b. Then we run it a second time and note how the x and status columns change. And when the MERGE query is run a third time the columns are updated again.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | test=# truncate b; TRUNCATE TABLE test=# select * from b; id | x | status ----+---+-------- (0 rows) merge into b using a on b.id = a.id when matched AND b.x > 2 THEN UPDATE SET x = b.x + a.x, status='updated+' when matched and b.x = 1 THEN UPDATE SET status = 'updated', x = 3 when not matched then insert (id,x,status) values (a.id,a.x,a.status); |
Now run the MERGE query once.
1 2 3 4 5 | test=# select * from b; id | x | status ----+---+------------ 1 | 1 | From a (1 row) |
Now run it again.
1 2 3 4 5 | test=# select * from b; id | x | status ----+---+------------ 1 | 3 | updated (1 row) |
And now a third time!
1 2 3 4 5 | test=# select * from b; id | x | status ----+---+------------ 1 | 4 | updated+ (1 row) |
The logic can be as complex as you can stand.
Conclusion
MERGE is extremely powerful and even these very simple examples provide a glimpse of its use. Many years ago I was working on a project that had transaction logs from cash registers around the world that had to coalesce and MERGE would have been heavily used if it was available. Read through the documentation and this blog and I am sure you will start thinking about some of your more common queries and how they can benefit from the MERGE operator.
As your tables grow larger be sure to double-check that you are using indexes on the proper columns and use EXPLAIN to double-check.
This is a great addition to PostgreSQL. Congrats to them for implementing it!
Unfortunately, MySQL has not implemented it yet. The request for this feature was made in 2005: https://bugs.mysql.com/bug.php?id=9018