MERGE

The MERGE statement consolidates insert, update, and delete operations into a single command. You specify a target table to modify and a source table (or subquery) that provides new data, then define WHEN MATCHED and WHEN NOT MATCHED clauses that determine what happens to each row.

MERGE syntax and concepts

The basic structure of a MERGE statement:

MERGE INTO target_table
USING source_table ON match_condition
WHEN MATCHED AND condition THEN
  UPDATE SET column1 = value1, column2 = value2
WHEN MATCHED AND NOT condition THEN
  DELETE
WHEN NOT MATCHED THEN
  INSERT (column1, column2) VALUES (value1, value2)
RETURNING merge_action(), target_table.*;

Three key concepts: the target table is what you modify; the source table provides incoming data; the match condition in the ON clause links the two. You can include multiple WHEN clauses with additional AND conditions to route each row to a different action.

Practical MERGE example with RETURNING

Set up a products table and a table of incoming updates:

CREATE TABLE products (
  product_id  SERIAL PRIMARY KEY,
  name        TEXT UNIQUE,
  price       DECIMAL(10, 2),
  stock       INTEGER,
  status      TEXT,
  last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO products (name, price, stock, status)
VALUES ('Laptop',   999.99, 50,  'active'),
       ('Keyboard',  79.99, 100, 'active'),
       ('Mouse',     29.99, 200, 'active');

CREATE TABLE product_updates (
  name   TEXT,
  price  DECIMAL(10, 2),
  stock  INTEGER,
  status TEXT
);

INSERT INTO product_updates VALUES
  ('Laptop',     1099.99, 75,  'active'),       -- update price and stock
  ('Monitor',     299.99, 30,  'active'),        -- new product
  ('Keyboard',      NULL,  0,  'discontinued'),  -- delete this product
  ('Headphones',   89.99, 50,  'active');        -- new product

Run the merge and capture what happened with RETURNING (PostgreSQL 17+):

MERGE INTO products p
USING product_updates u ON p.name = u.name
WHEN MATCHED AND u.status = 'discontinued' THEN
  DELETE
WHEN MATCHED AND u.status = 'active' THEN
  UPDATE SET
    price        = COALESCE(u.price, p.price),
    stock        = u.stock,
    status       = u.status,
    last_updated = CURRENT_TIMESTAMP
WHEN NOT MATCHED AND u.status = 'active' THEN
  INSERT (name, price, stock, status)
  VALUES (u.name, u.price, u.stock, u.status)
RETURNING merge_action() AS action,
          p.product_id, p.name, p.price, p.stock, p.status;

 action | product_id |    name    |   price  | stock | status
--------+------------+------------+----------+-------+--------
 UPDATE |          1 | Laptop     | 1099.99  |   75  | active
 INSERT |          4 | Monitor    |  299.99  |   30  | active
 DELETE |          2 | Keyboard   |   79.99  |  100  | active
 INSERT |          5 | Headphones |   89.99  |   50  | active

Production tips for MERGE

• Index the columns used in the ON match condition on both tables to avoid sequential scans on large source sets.
• Ensure the ON condition produces at most one source row per target row; ambiguous matches cause a cardinality violation error.
• Use a CTE or subquery as the source to clean or deduplicate data before the merge: USING (SELECT DISTINCT ON (name) * FROM product_updates ORDER BY name) u ON ....
• Use COALESCE(u.price, p.price) in the SET clause to preserve existing values when the incoming row has NULLs.
• Wrap the entire MERGE in an explicit transaction when it is part of a larger data pipeline so partial failures roll back cleanly.