Upsert & MERGE

Intermediate · 22 min read · ▶ live playground · ✦ checkpoint

An upsert is the write you want when a row might already exist: insert it if absent, update it if present. The important part is not the nickname. The important part is that the database makes that decision in one statement, using a unique key as the line between "new row" and "same row."

In the café inventory below, item_code is the primary key. That is the conflict target: there can be only one row for COF-ESP, one row for TEA-EARL, and so on.

The key that makes upsert possible

Start by inspecting the current rows. No write yet — just the target table and the key you will use.

SELECT item_code, item_name, quantity, checked_on
FROM inventory_counts
ORDER BY item_code;
 item_code |   item_name    | quantity | checked_on
-----------+----------------+----------+------------
 COF-ESP   | Espresso Beans |       12 | 2026-05-01
 TEA-EARL  | Earl Grey      |        8 | 2026-05-01
(2 rows)

If a table has no unique rule, the database cannot know what "already exists" means. ON CONFLICT (item_code) only works because item_code is unique. A duplicate item name would be a messy data problem; a duplicate primary key is a conflict the engine can arbitrate.

ON CONFLICT DO UPDATE

The next delivery count has one existing item and one new item. Because this write can update existing rows, rehearse both the current target row and the proposed row before you run it:

WITH proposed(item_code, item_name, quantity, checked_on) AS (
  VALUES
    ('COF-ESP', 'Espresso Beans', 15, DATE '2026-05-02'),
    ('BAK-CRO', 'Croissant', 6, DATE '2026-05-02')
)
SELECT p.item_code,
       ic.quantity AS current_quantity,
       p.quantity AS proposed_quantity,
       CASE
         WHEN ic.item_code IS NULL THEN 'will insert'
         ELSE 'will update'
       END AS path
FROM proposed AS p
LEFT JOIN inventory_counts AS ic
  ON ic.item_code = p.item_code
ORDER BY p.item_code;
 
INSERT INTO inventory_counts (item_code, item_name, quantity, checked_on)
VALUES
  ('COF-ESP', 'Espresso Beans', 15, DATE '2026-05-02'),
  ('BAK-CRO', 'Croissant', 6, DATE '2026-05-02')
ON CONFLICT (item_code) DO UPDATE
SET quantity = EXCLUDED.quantity,
    checked_on = EXCLUDED.checked_on;
 
SELECT item_code, item_name, quantity, checked_on
FROM inventory_counts
ORDER BY item_code;
 item_code | current_quantity | proposed_quantity |    path
-----------+------------------+-------------------+-------------
 BAK-CRO   |             NULL |                 6 | will insert
 COF-ESP   |               12 |                15 | will update
(2 rows)
INSERT 0 2
 item_code |   item_name    | quantity | checked_on
-----------+----------------+----------+------------
 BAK-CRO   | Croissant      |        6 | 2026-05-02
 COF-ESP   | Espresso Beans |       15 | 2026-05-02
 TEA-EARL  | Earl Grey      |        8 | 2026-05-01
(3 rows)

EXCLUDED is PostgreSQL's name for the row you tried to insert but could not, because it conflicted with an existing key. In the COF-ESP row, EXCLUDED.quantity is 15, so the existing row is updated to the proposed count. In the BAK-CRO row, there is no conflict, so the row is inserted normally.

Notice the command tag: PostgreSQL still labels the statement INSERT because that is the statement you ran. For INSERT ... ON CONFLICT DO UPDATE, the final count covers rows handled by the statement, whether each row inserted normally or took the update branch. It does not mean both rows were newly inserted.

The PostgreSQL spelling is ON CONFLICT ... DO UPDATE: a unique rule defines sameness, and the database chooses insert or update inside the write.

Idempotent feeds

Upserts are why import jobs can be retried without creating duplicate business rows. If the same inventory count arrives again for the same item_code, the conflict target sends it to the update branch instead of making a second COF-ESP.

That does not mean the statement is magic. The update branch still needs the same scrutiny as any update: rehearse the proposed rows, name the conflict target, and set only the columns you intend to replace. In the example above, the feed updates quantity and checked_on; it deliberately leaves item_name alone when a row already exists.

MERGE: one source, multiple branches

MERGE is the standard-shaped cousin of upsert. It shines when your source is already a table or query and you want to state the branches explicitly: when matched, update; when not matched, insert.

First rehearse the source rows against the target:

SELECT sf.item_code,
       ic.quantity AS current_quantity,
       sf.quantity AS source_quantity,
       CASE
         WHEN ic.item_code IS NULL THEN 'will insert'
         ELSE 'will update'
       END AS path
FROM stock_feed AS sf
LEFT JOIN inventory_counts AS ic
  ON ic.item_code = sf.item_code
ORDER BY sf.item_code;
 
WITH merged AS (
  MERGE INTO inventory_counts AS ic
  USING stock_feed AS sf
  ON ic.item_code = sf.item_code
  WHEN MATCHED THEN
    UPDATE SET quantity = sf.quantity,
               checked_on = sf.checked_on
  WHEN NOT MATCHED THEN
    INSERT (item_code, item_name, quantity, checked_on)
    VALUES (sf.item_code, sf.item_name, sf.quantity, sf.checked_on)
  RETURNING merge_action() AS action,
            ic.item_code,
            ic.quantity,
            ic.checked_on
)
SELECT action, item_code, quantity, checked_on
FROM merged
ORDER BY item_code;
 
SELECT item_code, item_name, quantity, checked_on
FROM inventory_counts
ORDER BY item_code;
 item_code | current_quantity | source_quantity |    path
-----------+------------------+-----------------+-------------
 COF-DECAF |             NULL |               4 | will insert
 COF-ESP   |               15 |              18 | will update
 TEA-EARL  |                8 |               7 | will update
(3 rows)
 action | item_code | quantity | checked_on
--------+-----------+----------+------------
 INSERT | COF-DECAF |        4 | 2026-05-03
 UPDATE | COF-ESP   |       18 | 2026-05-03
 UPDATE | TEA-EARL  |        7 | 2026-05-03
(3 rows)
 item_code |   item_name    | quantity | checked_on
-----------+----------------+----------+------------
 BAK-CRO   | Croissant      |        6 | 2026-05-02
 COF-DECAF | Decaf Beans    |        4 | 2026-05-03
 COF-ESP   | Espresso Beans |       18 | 2026-05-03
 TEA-EARL  | Earl Grey      |        7 | 2026-05-03
(4 rows)

merge_action() tells you which branch each returned row took. Lesson 8.4 will make RETURNING a general tool; here it is especially useful because one MERGE can insert some rows and update others.

Use ON CONFLICT when you are inserting rows into one table and the conflict rule is the whole decision. Reach for MERGE when a source table or source query drives clearer matched and not-matched branches.

sql — playgroundlive
⌘/Ctrl + Enter to run

You now have the two main insert-or-update shapes: PostgreSQL's compact ON CONFLICT and SQL's explicit MERGE. Next lesson pulls RETURNING into the spotlight and then switches from hand-sized writes to bulk loading.

Checkpoint

Answer all three to mark this lesson complete

+50 XP on completion