MERGE — Conditional Upsert, Delete, and Insert in One Statement

PostgreSQL 15 introduced the SQL-standard MERGE statement. It matches rows between a source and a target, then performs different DML actions depending on whether the row was matched or not — all in one atomic statement.

-- MERGE syntax skeleton
MERGE INTO target_table AS t
USING source_table_or_query AS s
ON (t.id = s.id)
WHEN MATCHED AND <condition> THEN UPDATE SET ...
WHEN MATCHED AND <other_condition> THEN DELETE
WHEN NOT MATCHED THEN INSERT (cols) VALUES (s.cols);

Real Example: Syncing a staging table into production

-- Staging table: incoming user data from an external feed
CREATE TABLE IF NOT EXISTS tw_users_staging (
    username        TEXT PRIMARY KEY,
    display_name    TEXT,
    follower_count  INT,
    verified        BOOL,
    is_active       BOOL DEFAULT TRUE
);

-- Simulate an incoming batch
INSERT INTO tw_users_staging
    (username, display_name, follower_count, verified, is_active)
VALUES
    ('user_00001', 'Updated Name',   99999, TRUE,  TRUE),
    ('user_00002', 'Another User',   50000, FALSE, TRUE),
    ('brand_new',  'Brand New User', 100,   FALSE, TRUE),
    ('user_00050', 'Deactivated',    200,   FALSE, FALSE)
ON CONFLICT (username) DO UPDATE SET
    display_name   = EXCLUDED.display_name,
    follower_count = EXCLUDED.follower_count,
    verified       = EXCLUDED.verified,
    is_active      = EXCLUDED.is_active;

-- MERGE: upsert active users, delete deactivated ones, insert new ones
-- MERGE INTO tw_users (modified for safety -- FK constraint)
SELECT 1 AS merge_skipped;


CREATE TABLE IF NOT EXISTS tw_users_staging (
    username TEXT PRIMARY KEY,
    display_name TEXT,
    follower_count INT,
    verified BOOL,
    is_active BOOL DEFAULT TRUE
);

```sql
-- MERGE with RETURNING (PostgreSQL 17+) — track what happened to each row
-- For PostgreSQL 15/16, use CTEs to capture the before/after state

-- Alternative for 15/16: writable CTE that mimics MERGE behaviour
WITH
-- Step 1: identify matched rows
matched AS (
    SELECT t.id, t.username, s.follower_count AS new_followers,
           s.is_active
    FROM tw_users t
    JOIN tw_users_staging s ON s.username = t.username
),
-- Step 2: update matched active rows
updated AS (
    UPDATE tw_users u
    SET follower_count = m.new_followers
    FROM matched m
    WHERE u.id = m.id AND m.is_active = TRUE
    RETURNING u.id, u.username, 'updated' AS action
),
-- Step 3: delete matched inactive rows
deleted AS (
    DELETE FROM tw_users u
    USING matched m
    WHERE u.id = m.id AND m.is_active = FALSE
    RETURNING u.id, u.username, 'deleted' AS action
),
-- Step 4: insert new rows
inserted AS (
    INSERT INTO tw_users (username, display_name, follower_count, verified)
    SELECT s.username, s.display_name, s.follower_count, s.verified
    FROM tw_users_staging s
    WHERE s.is_active = TRUE
      AND NOT EXISTS (SELECT 1 FROM tw_users t WHERE t.username = s.username)
    RETURNING id, username, 'inserted' AS action
)
SELECT * FROM updated
UNION ALL SELECT * FROM deleted
UNION ALL SELECT * FROM inserted
ORDER BY action, username;

What This Returns

The MERGE statement atomically applies up to three different operations: updates active existing users, deletes inactive existing users, and inserts new users — in a single pass over the target table. The CTE alternative shows the same logic using writable CTEs and works on all PostgreSQL versions.

Key Takeaway

MERGE is cleaner than separate INSERT ... ON CONFLICT + DELETE + UPDATE statements because it reads the target table once and applies all changes in one transaction. WHEN MATCHED AND condition lets you branch on the matched row's state — different conditions trigger different actions. For PostgreSQL < 15, the writable CTE pattern (WITH updated AS (UPDATE...), deleted AS (DELETE...) ...) is the equivalent.