In PostgreSQL version 15, the merge command was introduced, and we can use the merge command to perform update, insert, and delete operations in the source table based on the matching rows from the target table. Without the merge command, we need to manually perform many more steps.
We need to manually check the rows that exist in the source table, and if it exist’s, we need to manually updated it or insert them, whatever is needed. We can check the description of the MERGE command in Postgres like this.
\h merge
Result:
Command: MERGE
Description: conditionally insert, update, or delete rows of a table
Syntax:
[ WITH with_query [, ...] ]
MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
USING data_source ON join_condition
when_clause [...]
[ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]
{ * | output_expression [ [ AS ] output_name ] } [, ...] ]
where data_source is:
{ [ ONLY ] source_table_name [ * ] | ( source_query ) } [ [ AS ] source_alias ]
and when_clause is:
{ WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
WHEN NOT MATCHED BY SOURCE [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
WHEN NOT MATCHED [ BY TARGET ] [ AND condition ] THEN { merge_insert | DO NOTHING } }
and merge_insert is:
INSERT [( column_name [, ...] )]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }
and merge_update is:
UPDATE SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]
and merge_delete is:
DELETE
URL: https://www.postgresql.org/docs/19/sql-merge.html
Create sample tables for testing and insert some sample values like this.
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name TEXT,
department TEXT,
salary NUMERIC(10,2),
status TEXT
);
INSERT INTO employees VALUES
(1, 'Alice', 'HR', 40000, 'Active'),
(2, 'Bob', 'IT', 60000, 'Active'),
(3, 'Charlie', 'Finance', 55000, 'Active'),
(4, 'David', 'Sales', 45000, 'Inactive');
CREATE TABLE employee_updates (
emp_id INT,
emp_name TEXT,
department TEXT,
salary NUMERIC(10,2),
status TEXT
);
INSERT INTO employee_updates VALUES
(2, 'Bob', 'IT', 70000, 'Active'),
(3, 'Charlie','Finance', 56000, 'Inactive'),
(5, 'Emma', 'Marketing', 50000, 'Active'),
(6, 'Frank', 'HR', 45000, 'Active');
Check the results from both tables.
SELECT * FROM employees;
Result :
emp_id | emp_name | department | salary | status
--------+----------+------------+----------+----------
1 | Alice | HR | 40000.00 | Active
2 | Bob | IT | 60000.00 | Active
3 | Charlie | Finance | 55000.00 | Active
4 | David | Sales | 45000.00 | Inactive
(4 rows)
Check the results from the employee_updates table also.
SELECT * FROM employee_updates;
Result :
emp_id | emp_name | department | salary | status
--------+----------+------------+----------+----------
2 | Bob | IT | 70000.00 | Active
3 | Charlie | Finance | 56000.00 | Inactive
5 | Emma | Marketing | 50000.00 | Active
6 | Frank | HR | 45000.00 | Active
(4 rows)
Now use the MERGE command with the WHEN MATCHED clause and WHEN NOT MATCHED.
MERGE INTO employees e
USING employee_updates u
ON e.emp_id = u.emp_id
WHEN MATCHED THEN
UPDATE
SET salary = u.salary,
department = u.department,
status = u.status
WHEN NOT MATCHED THEN
INSERT (emp_id, emp_name, department, salary, status)
VALUES (
u.emp_id,
u.emp_name,
u.department,
u.salary,
u.status
);
Now, in the above example, we check the rows to perform the update based on the emp_id from both tables.
For the unmatched rows, we execute an insert operation in the employees table based on the employee_updates table.
Now check the results from the employees table like this.
select * from employees;
Result :
emp_id | emp_name | department | salary | status
--------+----------+------------+----------+----------
1 | Alice | HR | 40000.00 | Active
4 | David | Sales | 45000.00 | Inactive
2 | Bob | IT | 70000.00 | Active
3 | Charlie | Finance | 56000.00 | Inactive
5 | Emma | Marketing | 50000.00 | Active
6 | Frank | HR | 45000.00 | Active
(6 rows)
In the same way, we can perform deletion also.
MERGE INTO employees e
USING employee_updates u
ON e.emp_id = u.emp_id
WHEN MATCHED
AND u.salary > e.salary
THEN
UPDATE
SET salary = u.salary;
MERGE INTO employees e
USING employee_updates u
ON e.emp_id = u.emp_id
WHEN MATCHED
AND u.status = 'Inactive'
THEN
DELETE;
The first merge command query checks the matching rows and checks the salary of condition and when it is satisfied, then it updates.
The second merge command query checks the matching rows and check the status is inactive, and then performs the delete operation.
Check the results from the employees table.
select * from employees;
Result :
emp_id | emp_name | department | salary | status
--------+----------+------------+----------+----------
1 | Alice | HR | 40000.00 | Active
4 | David | Sales | 45000.00 | Inactive
2 | Bob | IT | 70000.00 | Active
5 | Emma | Marketing | 50000.00 | Active
6 | Frank | HR | 45000.00 | Active
(5 rows)
With the merge command, we can also use the DO NOTHING clause also.
MERGE INTO employees e
USING employee_updates u
ON e.emp_id = u.emp_id
WHEN MATCHED THEN
DO NOTHING
WHEN NOT MATCHED THEN
INSERT (emp_id, emp_name, department, salary, status)
VALUES (
u.emp_id,
u.emp_name,
u.department,
u.salary,
u.status
);
Check the results again.
select * from employees;
Result :
emp_id | emp_name | department | salary | status
--------+----------+------------+----------+----------
1 | Alice | HR | 40000.00 | Active
4 | David | Sales | 45000.00 | Inactive
2 | Bob | IT | 70000.00 | Active
5 | Emma | Marketing | 50000.00 | Active
6 | Frank | HR | 45000.00 | Active
3 | Charlie | Finance | 56000.00 | Inactive
(6 rows)
MERGE INTO employees e
USING employee_updates u
ON e.emp_id = u.emp_id
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
In the merge command, actually we need to understand two types of tables.
- Source table - The table being modified during the merge command
- Target table - The table supplying the data during the merge command.
So here we are checking the matching rows, and we perform a delete operation based on the condition WHEN NOT MATCHED BY SOURCE.
The meaning of WHEN NOT MATCHED BY SOURCE in merge command is that, check the row exist from source table is exist on target table, if it is not matched, then execute the delete operation.
Check the results again.
select * from employees;
Result :
emp_id | emp_name | department | salary | status
--------+----------+------------+----------+----------
2 | Bob | IT | 70000.00 | Active
5 | Emma | Marketing | 50000.00 | Active
6 | Frank | HR | 45000.00 | Active
3 | Charlie | Finance | 56000.00 | Inactive
(4 rows)
In the same way, we can execute the update command also based on the WHEN NOT MATCHED BY SOURCE clause.
MERGE INTO employees e
USING employee_updates u
ON e.emp_id = u.emp_id
WHEN NOT MATCHED BY SOURCE THEN
UPDATE
SET status='Inactive';
In the same way, we can use the DO NOTHING clause also.
MERGE INTO employees e
USING employee_updates u
ON e.emp_id = u.emp_id
WHEN NOT MATCHED BY SOURCE THEN
DO NOTHING;
Now include all these usages into a single merge command like this.
MERGE INTO employees e
USING employee_updates u
ON e.emp_id=u.emp_id
WHEN MATCHED
AND u.status='Inactive'
THEN
DELETE
WHEN MATCHED
THEN
UPDATE
SET salary=u.salary,
department=u.department
WHEN NOT MATCHED
THEN
INSERT (
emp_id,
emp_name,
department,
salary,
status
)
VALUES (
u.emp_id,
u.emp_name,
u.department,
u.salary,
u.status
);
Now check the results again from the employee table.
select * from employees;
Result :
emp_id | emp_name | department | salary | status
--------+----------+------------+----------+--------
2 | Bob | IT | 70000.00 | Active
5 | Emma | Marketing | 50000.00 | Active
6 | Frank | HR | 45000.00 | Active
(3 rows)
We can also use the RETURNING clause with the merge command like this.
MERGE INTO employees e
USING employee_updates u
ON e.emp_id=u.emp_id
WHEN MATCHED THEN
UPDATE
SET salary=u.salary
WHEN NOT MATCHED THEN
INSERT (
emp_id,
emp_name,
department,
salary,
status
)
VALUES (
u.emp_id,
u.emp_name,
u.department,
u.salary,
u.status
)
RETURNING *;
Now this will return the newly inserted records because we used the returning clause here.
emp_id | emp_name | department | salary | status | emp_id | emp_name | department | salary | status
--------+----------+------------+----------+----------+--------+----------+------------+----------+----------
2 | Bob | IT | 70000.00 | Active | 2 | Bob | IT | 70000.00 | Active
3 | Charlie | Finance | 56000.00 | Inactive | 3 | Charlie | Finance | 56000.00 | Inactive
5 | Emma | Marketing | 50000.00 | Active | 5 | Emma | Marketing | 50000.00 | Active
6 | Frank | HR | 45000.00 | Active | 6 | Frank | HR | 45000.00 | Active
(4 rows)
The MERGE command provides a simple way to handle INSERT, UPDATE, and DELETE operations in a single statement. It helps keep data synchronized between tables while reducing the number of separate SQL queries. By understanding clauses such as WHEN MATCHED, WHEN NOT MATCHED, WHEN NOT MATCHED BY SOURCE, DO NOTHING, and RETURNING, we can write cleaner and more efficient postgres queries for different data synchronization tasks.