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.

Terminology

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.

Example of how it works

Let’s start with two tables and one row of data.

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.

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.

If we use EXPLAIN on the query we get the following:

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.

Run MERGE again

If you run the same query a second time, this time the matched branch runs, and the value of x increases.

Each time you run the query again, the value of x in table b continues to increase.

DELETEs too!

MERGE isn’t limited to inserts and updates. You can also delete rows from the target table when a match occurs.

DOING nothing

Sometimes you don’t want to make any changes at all if a row already exists. That’s where DO NOTHING comes in.

COMPLEXITY too!

MERGE can handle more advanced logic with multiple conditions. In the example below, different updates are applied depending on the value of x.

After running it once:

Run it a second time:

And a third time:

The flexibility of MERGE makes it a powerful addition to your toolkit.

Conclusion

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.

Enterprise-grade PostgreSQL without trade-offs

Subscribe
Notify of
guest

1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Bill Karwin

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

2
Reply