âš¡IntermediateCTEs
Upsert: INSERT ON CONFLICT and MERGE
Insert or update rows atomically using ON CONFLICT and the MERGE statement.
Overview
Upsert combines INSERT and UPDATE into one atomic operation. If the row doesn't exist, insert it; if it does (conflict on unique/PK), update it. PostgreSQL supports two syntaxes: INSERT ... ON CONFLICT (the original, widely used) and MERGE (SQL standard, added in PostgreSQL 15).
ON CONFLICT DO UPDATE uses EXCLUDED to reference the proposed row values. ON CONFLICT DO NOTHING silently skips conflicting inserts.
Key Points
- •INSERT ... ON CONFLICT (col) DO UPDATE SET col = EXCLUDED.col
- •EXCLUDED refers to the row that was attempted to be inserted
- •ON CONFLICT DO NOTHING silently skip duplicate rows
- •MERGE (PG 15+): WHEN MATCHED THEN UPDATE / WHEN NOT MATCHED THEN INSERT
- •Specify the conflict target: ON CONFLICT (col) or ON CONFLICT ON CONSTRAINT name
- •Upsert is atomic no race condition between check and insert
Examples
INSERT ON CONFLICT and MERGE
-- Upsert: update score if user exists, insert if new
INSERT INTO user_scores (user_id, score)
VALUES (42, 100)
ON CONFLICT (user_id) DO UPDATE
SET score = GREATEST(user_scores.score, EXCLUDED.score),
updated_at = NOW();
-- MERGE (PostgreSQL 15+)
MERGE INTO inventory AS target
USING new_stock AS source ON target.product_id = source.product_id
WHEN MATCHED THEN
UPDATE SET qty = target.qty + source.qty
WHEN NOT MATCHED THEN
INSERT (product_id, qty) VALUES (source.product_id, source.qty);Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4