INSERT, UPDATE, DELETE, upserts and MERGE

conceptThe concept

INSERT ... RETURNING id returns generated keys and defaults in the same round trip. UPDATE and DELETE support RETURNING too, and UPDATE ... FROM / DELETE ... USING let you join other tables to decide which rows to change.

Upsert: INSERT ... ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col inserts or updates atomically, even under concurrency, as long as there is a unique index or constraint on the conflict target. EXCLUDED refers to the row that was proposed for insertion. DO NOTHING skips duplicates.

MERGE (SQL standard) matches a source against a target and runs different actions for matched and unmatched rows, including deletes. Unlike ON CONFLICT, it does not rely on a unique index, but it also does not give the same guarantee against concurrent inserts of the same key.

whyWhy it matters

RETURNING removes a round trip and a race (insert, then select back). Upserts make ingestion jobs safely re-runnable: replaying yesterday's feed should not create duplicates.

Use caseReal-world scenario

A supplier sends a stock feed every hour. Known SKUs get their stock updated, new SKUs are created, and the job can crash and re-run without harm.

Try itExamples

Run and edit these in the interactive lesson.

Wrapped in a transaction that is rolled back, so the shop data stays unchanged.

BEGIN;
INSERT INTO customers (name, email, city, country, created_at)
VALUES ('Ada Lovelace', 'ada@example.com', 'London', 'UK', now())
RETURNING id, tier, created_at;

UPDATE products SET price = round(price * 1.05, 2)
WHERE category = 'books' AND stock = 0
RETURNING sku, price;

DELETE FROM reviews
WHERE rating = 1 AND created_at < '2024-02-01'
RETURNING id, product_id;
ROLLBACK;

BEGIN;
INSERT INTO products (sku, name, category, price, stock)
VALUES ('SKU-0001', 'ignored on conflict', 'books', 1.00, 5),
       ('SKU-9001', 'Field Guide', 'books', 24.50, 40)
ON CONFLICT (sku) DO UPDATE
  SET stock = EXCLUDED.stock
RETURNING sku, name, stock, (xmax::text <> '0') AS was_update;
ROLLBACK;

BEGIN;
MERGE INTO products AS p
USING (VALUES ('SKU-0002', 50), ('SKU-0003', 0), ('SKU-9002', 7)) AS feed(sku, stock)
   ON p.sku = feed.sku
WHEN MATCHED AND feed.stock = 0 THEN
  UPDATE SET active = false, stock = 0
WHEN MATCHED THEN
  UPDATE SET stock = feed.stock
WHEN NOT MATCHED THEN
  INSERT (sku, name, category, price, stock) VALUES (feed.sku, 'Imported item', 'home', 9.99, feed.stock)
RETURNING merge_action(), p.sku, p.stock, p.active;
ROLLBACK;

BEGIN;
UPDATE orders AS o
SET status = 'cancelled'
FROM customers AS c
WHERE c.id = o.customer_id
  AND c.tier = 'platinum'
  AND o.status = 'pending'
RETURNING o.id, c.name, o.status;
ROLLBACK;

internalsUnder the hood

An UPDATE in PostgreSQL is a delete plus an insert at the storage level: it writes a new row version and marks the old one dead (MVCC). That is why heavy update workloads need VACUUM, and why xmax on a freshly upserted row is non-zero when the conflict path updated it.

Avoid / preferDo this, not that

SELECT 1 FROM customers WHERE email = $1;
-- not found, so
INSERT INTO customers (...) VALUES (...);

INSERT INTO customers (...) VALUES (...)
ON CONFLICT (email) DO NOTHING
RETURNING id;

DELETE FROM events WHERE at < now() - interval '90 days';  -- 200 million rows

DELETE FROM events
WHERE id IN (SELECT id FROM events WHERE at < now() - interval '90 days' LIMIT 10000);
-- repeat until no rows; or drop old partitions

PitfallsCommon mistakes

• Implementing upsert as SELECT then INSERT in the application. Two sessions can both see "missing" and both insert. ON CONFLICT is atomic.
• Expecting ON CONFLICT to work without a unique index or constraint on the target columns. It raises an error instead.
• Running a large UPDATE in one statement on a busy table: it holds row locks for the whole duration and doubles the table in dead tuples until VACUUM. Batch it.

LimitsWhen not to use it

Prefer INSERT ... ON CONFLICT over MERGE for concurrent upserts: MERGE can still fail with a unique violation when two sessions insert the same key at once, while ON CONFLICT is designed for that race.

SecuritySecurity notes

Run ad hoc UPDATE and DELETE inside BEGIN, check the row count and RETURNING output, then COMMIT. Grant DELETE only to roles that need it; most application roles can soft-delete with an UPDATE instead.

ChallengeTry it yourself

BEGIN;
WITH changed AS (
  UPDATE orders SET status = 'cancelled'
  WHERE status = 'pending' AND created_at < '2025-06-01'
  RETURNING customer_id
)
SELECT c.tier, count(*) AS cancelled
FROM changed
JOIN customers c ON c.id = changed.customer_id
GROUP BY c.tier
ORDER BY c.tier;
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• INSERT and ON CONFLICT
• MERGE