This blog was first authored in 2022. We’ve updated it in 2025 for clarity and relevance, reflecting current practices while honoring their original perspective.
PostgreSQL 15 introduced the MERGE command, a feature that has a big impact on how you can manage data updates. With MERGE, you can combine inserts, updates, and deletes into a single statement—removing the need to push this work into your application code.
Think about a scenario where you’re managing product inventory. Sometimes you need to add new items, sometimes you need to adjust quantities, and sometimes you may need to remove products altogether. In the past, you’d handle this with multiple queries or through the application layer, moving data back and forth. Now you can do it all directly in the database with a single MERGE statement.
This operator has been part of the SQL standard for years and has long existed in SQL Server and Oracle. PostgreSQL brought it in with version 15, giving you the ability to handle multiple operations at once. While the syntax may seem more complex than a simple “upsert,” it gives you much more control.
When working with MERGE, you’ll use two tables:
Source table: the data you want to apply.
Target table: the table you’re updating.
PostgreSQL checks rows between the two tables and applies the actions you specify depending on whether a match exists.
Let’s start with two tables and one row of data.
|
1 |
test=# create table a (id int, x int, status char(10));<br>CREATE TABLE<br>test=# create table b (id int, x int, status char(10));<br>CREATE TABLE<br>test=# insert into a (id,x,status) values (1,1,'From a');<br>INSERT 0 1<br>test=# select * from a;select * from b;<br>id | x | status<br>----+---+------------<br>1 | 1 | From a<br>(1 row)<br><br>id | x | status<br>----+---+--------<br>(0 rows)<br><br> |
Now we can use MERGE. In this case, table a has one row, and table b is empty. The query will insert rows that aren’t in the target table, and update rows that already exist.
|
1 |
MERGE into b using a on a.id = b.id<br>when matched then <br> update set x = b.x + 1<br>when not matched then <br> insert (id,x,status) values (a.id,a.x,a.status);<br> |
The server compares the two tables on the id column. If there’s a match, x in table b is incremented. If there’s no match, the row from table a is inserted into table b.
|
1 |
test-# update set x = b.x + 1<br>test-# when not matched then insert (id,x,status) values (a.id,a.x,a.status);<br> QUERY PLAN<br>----------------------------------------------------------------<br> Merge on b (cost=0.00..1.02 rows=0 width=0)<br> -> Nested Loop Left Join (cost=0.00..1.02 rows=1 width=25)<br> Join Filter: (a.id = b.id)<br> -> Seq Scan on a (cost=0.00..1.01 rows=1 width=19)<br> -> Seq Scan on b (cost=0.00..0.00 rows=1 width=10)<br>(5 rows)<br><br><br>test=#<br> |
A quick note: the correct syntax is update set x = b.x + 1. If you write update set b.x = b.x + 1, PostgreSQL will throw a syntax error. Also, you can only update the target table, not the source.
If you run the same query a second time, this time the matched branch runs, and the value of x increases.
|
1 |
test=# select * from a; select * from b;<br>id | x | status<br>----+---+------------<br>1 | 1 | From a<br>(1 row)<br><br><br>id | x | status<br>----+---+------------<br>1 | 2 | From a<br>(1 row)<br><br><br>test=# |
Each time you run the query again, the value of x in table b continues to increase.
MERGE isn’t limited to inserts and updates. You can also delete rows from the target table when a match occurs.
|
1 |
test=# merge into b using a on a.id = b.id<br>test-# when matched then delete<br>test-# when not matched then insert (id,x,status) values (a.id,a.x,a.status);<br>MERGE 1<br>test=# select * from b;<br> id | x | status<br>----+---+--------<br>(0 rows)<br> |
Sometimes you don’t want to make any changes at all if a row already exists. That’s where DO NOTHING comes in.
|
1 |
test=# MERGE into b using a on a.id = b.id<br>test-# when matched then<br>test-# DO NOTHING<br>test-# when not matched then<br>test-# insert (id,x,status) values (a.id,a.x,a.status);<br>MERGE 1<br>test=#<br> |
MERGE can handle more advanced logic with multiple conditions. In the example below, different updates are applied depending on the value of x.
|
1 |
test=# truncate b;<br>TRUNCATE TABLE<br>test=# select * from b;<br> id | x | status<br>----+---+--------<br>(0 rows)<br>merge into b<br>using a<br>on b.id = a.id<br>when matched AND b.x > 2 THEN<br>UPDATE SET x = b.x + a.x, status='updated+'<br>when matched and b.x = 1 THEN<br>UPDATE SET status = 'updated', x = 3<br>when not matched then<br>insert (id,x,status) values (a.id,a.x,a.status);<br> |
After running it once:
|
1 |
test=# select * from b;<br> id | x | status<br>----+---+------------<br> 1 | 1 | From a<br>(1 row)<br> |
Run it a second time:
|
1 |
test=# select * from b;<br> id | x | status<br>----+---+------------<br> 1 | 3 | updated<br>(1 row)<br> |
And a third time:
|
1 |
test=# select * from b;<br> id | x | status<br>----+---+------------<br> 1 | 4 | updated+<br>(1 row)<br> |
The flexibility of MERGE makes it a powerful addition to your toolkit.
MERGE brings new efficiency and clarity to PostgreSQL. It cuts down on application-side logic, reduces overhead, and allows you to handle inserts, updates, and deletes in one statement. As your datasets grow, be sure to use indexes effectively and rely on EXPLAIN to keep performance under control.
If MERGE has you thinking about what else PostgreSQL can do for your production workloads, you’re in the right place. Percona is the production-ready, fully open source alternative to costly proprietary solutions you can deploy anywhere. Explore our resources to learn how to cut costs, build smarter, and stay free from vendor lock-in.
