Managing data synchronization efficiently is a common challenge in modern applications, especially when dealing with external imports, staging environments, or duplicate inserts. PostgreSQL 15 introduced the powerful MERGE statement, enabling developers to handle INSERT, UPDATE, and DELETE operations conditionally within a single query.
In this blog, we’ll explore the syntax of MERGE, its real-world use cases, and a practical example that demonstrates its utility in solving a common business problem.
What is a MERGE Statement?
The MERGE statement allows you to merge data from a source table into a target table. Depending on whether a match is found, you can choose to:
- Update the existing rows,
- Insert new ones,
- Or even delete rows from the target.
Think of it as a more powerful alternative to INSERT ... ON CONFLICT DO UPDATE, with greater control and multiple conditional branches.
Syntax of MERGE
MERGE INTO target_table AS t
USING source_table AS s
ON t.key = s.key
WHEN MATCHED THEN
UPDATE SET column = s.column
WHEN NOT MATCHED THEN
INSERT (column1, column2) VALUES (s.column1, s.column2);
You can also use WHEN MATCHED AND condition THEN or even add a DELETE clause like:
WHEN MATCHED AND s.status = 'inactive' THEN
DELETE
Common Use Cases
Here are some real-world scenarios where MERGE is incredibly useful:
1. Data Synchronization from External Feeds
If you receive periodic data from a supplier or CRM system and want to update your local database, MERGE helps synchronize efficiently.
2. ETL Pipelines
During Extract-Transform-Load jobs, data may already exist in the target table. You can update or insert based on match conditions without writing multiple statements.
3. Soft Deletes
You can conditionally delete rows that are no longer present in the source feed or mark them as inactive.
4. Auditing & History Maintenance
You can insert into an audit table when updates happen using WHEN MATCHED conditions.
Real-Life Example: Syncing Product Catalog
Scenario
Imagine you're managing a retail inventory system. You regularly receive an updated product list from your supplier, containing product IDs, names, and prices. Some products may already exist and just need a price update, others are new and need to be inserted.
Step-by-Step Example
Target Table (products):
CREATE TABLE products (
id INT PRIMARY KEY,
name TEXT,
price NUMERIC
);
Source Table (supplier_products):
CREATE TABLE supplier_products (
id INT,
name TEXT,
price NUMERIC
);
Insert Sample Data
-- Existing data
INSERT INTO products VALUES (1, 'Pen', 10), (2, 'Notebook', 30);
-- New supplier data
INSERT INTO supplier_products VALUES
(2, 'Notebook Pro', 35), -- Updated
(3, 'Pencil', 5); -- New
The MERGE Query
ERGE INTO products AS p
USING supplier_products AS s
ON p.id = s.id
WHEN MATCHED THEN
UPDATE SET name = s.name, price = s.price
WHEN NOT MATCHED THEN
INSERT (id, name, price) VALUES (s.id, s.name, s.price);
Result
SELECT * FROM products;
id | name | price
----+--------------+-------
1 | Pen | 10
2 | Notebook Pro | 35
3 | Pencil | 5
(3 rows)
As you can see:
- Product with ID 2 was updated (Notebook ? Notebook Pro, 30 ? 35)
- Product with ID 3 was inserted
- Product with ID 1 remained unchanged
Points to Note
- Make sure to index the join condition column for performance.
- You can use filters in WHEN MATCHED AND to apply different logic for different rows.
- MERGE is transactional—any failure will roll back all changes.
The MERGE statement in PostgreSQL makes complex upsert logic easier, cleaner, and faster. Whether you're building a data warehouse, syncing with third-party systems, or maintaining dynamic datasets, MERGE can be your go-to tool for conflict-aware operations.
Start using MERGE in PostgreSQL 15+ today and simplify your multi-action queries into a single, powerful command!