The power of MERGE in PostgreSQL 15

The MERGE statement is a powerful tool that can be used to perform conditional INSERT, UPDATE, or DELETE operations on rows in a table. According Wikipedia MERGE operation was officially introduced in the SQL:2003 standard, and expanded in the SQL:2008 standard.

MERGE is similar to the UPSERT statement in other SQL dialects, but it offers a number of advantages, including:

It is more efficient, as it only performs the necessary operations for each row.
It is more atomic, as it either succeeds or fails as a whole.
It is more flexible, as it allows for more complex conditions to be specified.

Before PostgreSQL 15 merge was possible using INSERT INTO ... ON CONFLICT [ conflict_target ] conflict_action but now we can use all advantages of MERGE

The syntax for the MERGE statement is as follows:

MERGE INTO target_table AS t
USING source_table AS s
ON <condition>
WHEN MATCHED THEN
  <update_statement>
WHEN NOT MATCHED THEN
  <insert_statement>

The target_table is the table that will be updated or inserted into. The source_table is the table that contains the data that will be used to update or insert rows in the target_table. The condition is a Boolean expression that is used to determine whether a row in the source_table should be updated or inserted. The update_statement is a SQL statement that is used to update rows in the target_table. The insert_statement is a SQL statement that is used to insert rows into the target_table.

Here is an example of a MERGE statement that update table customers using leads table. Both tables have email field, so we need to add only leads that email not exists in customers table

MERGE INTO customers AS c
USING leads AS l
ON c.email = l.email
WHEN NOT MATCHED THEN
  insert (name, email, created_at)
  VALUES (l.name, l.email, DEFAULT);

This statement will first perform a join between the customers table and the leads table. The join will only return rows in the leads table where the email address is not exits in customers. For each row that is returned, the insert query will be performed.

This query equivalent next UPSERT query used before:

insert into customers (name, email)
select name, email from leads
on conflict (email) do nothing;

but if we look results we already see the MERGE advantage. It not increment id field in case when record already exists.

Just run both SQL queries and compare results

Let try more complicated task. Now we add leads with new email and update name for exist

MERGE INTO customers AS c
USING leads AS l
ON c.email = l.email
WHEN NOT MATCHED THEN
  INSERT (name, email, created_at)
  VALUES (l.name, l.email, DEFAULT)
WHEN MATCHED THEN UPDATE
    SET name = l.name
;

Here you can run SQL

And in last scenario we will add leads with absent emails, update name where customer creation date less then matched leads record and delete other:

MERGE INTO customers AS c
USING leads AS l
ON c.email = l.email
WHEN NOT MATCHED THEN
  INSERT (name, email, created_at)
  VALUES (l.email, l.name, DEFAULT)
WHEN MATCHED AND c.created_at < l.created_at THEN UPDATE
    SET name = l.name
WHEN MATCHED THEN
    DELETE
;

In article conclusion we can say:
The MERGE statement is a powerful tool that can be used to perform conditional INSERT, UPDATE, or DELETE operations on rows in a table. It is more efficient, more atomic, and more flexible than the UPSERT statement in other SQL dialects.