MERGE in PostgreSQL

The MERGE statement consolidates insert, update, and delete operations into a single atomic command. You specify a target table to modify and a source table (or subquery) that provides new data, then write WHEN MATCHED and WHEN NOT MATCHED clauses that determine what happens to each row. MERGE was introduced in PostgreSQL 15; PostgreSQL 17 extended it with RETURNING clause support.

Syntax

MERGE INTO target_table
USING source_table ON (match_condition)
WHEN MATCHED AND additional_condition THEN
  UPDATE SET column1 = value1
WHEN MATCHED AND other_condition THEN
  DELETE
WHEN NOT MATCHED THEN
  INSERT (column1, column2) VALUES (value1, value2);

With RETURNING (PostgreSQL 17+):

MERGE INTO target_table
USING source_table ON (match_condition)
WHEN MATCHED THEN UPDATE SET col = source.col
WHEN NOT MATCHED THEN INSERT (col) VALUES (source.col)
RETURNING merge_action(), target_table.*;

Practical Example

Set up a subscriptions table and a subscription_updates feed to demonstrate all three MERGE actions:

CREATE TABLE subscriptions (
  id           SERIAL PRIMARY KEY,
  account_ref  VARCHAR(50) UNIQUE NOT NULL,
  plan         VARCHAR(30) NOT NULL,
  monthly_rate NUMERIC(8, 2) NOT NULL,
  status       VARCHAR(20) NOT NULL DEFAULT 'active',
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO subscriptions (account_ref, plan, monthly_rate, status)
VALUES
  ('ACC-1001', 'starter',     19.00, 'active'),
  ('ACC-1002', 'professional', 49.00, 'active'),
  ('ACC-1003', 'enterprise',  199.00, 'active');

CREATE TABLE subscription_updates (
  account_ref  VARCHAR(50) NOT NULL,
  plan         VARCHAR(30),
  monthly_rate NUMERIC(8, 2),
  status       VARCHAR(20) NOT NULL
);

INSERT INTO subscription_updates (account_ref, plan, monthly_rate, status)
VALUES
  ('ACC-1001', 'professional', 49.00,  'active'),    -- upgrade existing account
  ('ACC-1002', NULL,           NULL,   'cancelled'), -- cancel existing account
  ('ACC-1004', 'starter',      19.00,  'active'),    -- new account
  ('ACC-1005', 'enterprise',  199.00,  'active');    -- another new account

Run the MERGE:

MERGE INTO subscriptions s
USING subscription_updates u ON (s.account_ref = u.account_ref)
WHEN MATCHED AND u.status = 'cancelled' THEN
  DELETE
WHEN MATCHED AND u.status = 'active' THEN
  UPDATE SET
    plan         = COALESCE(u.plan, s.plan),
    monthly_rate = COALESCE(u.monthly_rate, s.monthly_rate),
    status       = u.status,
    updated_at   = now()
WHEN NOT MATCHED AND u.status = 'active' THEN
  INSERT (account_ref, plan, monthly_rate, status)
  VALUES (u.account_ref, u.plan, u.monthly_rate, u.status);

Output:

MERGE 4

Verify the result:

SELECT account_ref, plan, monthly_rate, status
FROM subscriptions
ORDER BY account_ref;

 account_ref |     plan     | monthly_rate | status
-------------+--------------+--------------+--------
 ACC-1001    | professional |        49.00 | active
 ACC-1003    | enterprise   |       199.00 | active
 ACC-1004    | starter      |        19.00 | active
 ACC-1005    | enterprise   |       199.00 | active

ACC-1002 was deleted (cancelled), ACC-1001 was upgraded, and two new accounts were inserted.

Using RETURNING (PostgreSQL 17+)

RETURNING with merge_action() lets you audit exactly which action was taken on each row:

MERGE INTO subscriptions s
USING subscription_updates u ON (s.account_ref = u.account_ref)
WHEN MATCHED AND u.status = 'cancelled' THEN DELETE
WHEN MATCHED THEN UPDATE SET plan = u.plan, updated_at = now()
WHEN NOT MATCHED THEN INSERT (account_ref, plan, monthly_rate, status)
  VALUES (u.account_ref, u.plan, u.monthly_rate, u.status)
RETURNING merge_action() AS action, s.account_ref, s.plan;

 action | account_ref |     plan
--------+-------------+--------------
 UPDATE | ACC-1001    | professional
 DELETE | ACC-1002    | professional
 INSERT | ACC-1004    | starter
 INSERT | ACC-1005    | enterprise

Testing with Vela

MERGE is commonly used in ETL pipelines and nightly sync jobs that touch many rows across a large table. Use Vela database branching to run the MERGE against a production-like branch before deploying the pipeline. Inspect the RETURNING output to confirm the insert/update/delete counts match your expectations, then promote to production without risk. Branches are instant and copy-on-write, so no extra storage cost is incurred until rows are actually modified.

Production Tips

• Index the columns used in the ON match condition on both the target and source tables to avoid sequential scans on large datasets.
• Ensure the ON condition produces at most one source row per target row — ambiguous matches cause a cardinality violation error. Deduplicate the source with a CTE or DISTINCT ON first.
• Use COALESCE(u.col, s.col) in the SET clause to preserve existing values when the source row contains NULLs.
• Wrap the entire MERGE in an explicit transaction when it is part of a larger data pipeline so partial failures roll back cleanly.
• For very large source tables, consider processing in batches using a CTE with LIMIT as the source to avoid holding locks for extended periods.
• MERGE requires SELECT on the source and the appropriate INSERT, UPDATE, or DELETE privilege on the target for each WHEN clause action.
• For PostgreSQL 15 and 16, RETURNING is not available — use a CTE wrapping the MERGE and a follow-up SELECT for post-merge auditing.