Postgres Practice 6/12

Upsert: ON CONFLICT DO UPDATE

Restocking an item means "insert it if it's new, update it if it already exists" - the classic upsert. Written naively as a plain INSERT, it crashes the moment the row already exists: the primary key constraint rejects the duplicate. You could check first with a SELECT, then branch between INSERT and UPDATE - but that's two round trips and a race condition if two requests restock the same item at once.

ON CONFLICT (column) DO UPDATE handles it in one statement: try the insert, and if it collides with an existing primary key (or other unique constraint), run the given UPDATE instead. Inside that UPDATE, EXCLUDED refers to the row you were trying to insert - so you can combine it with the row already there.

You have an inventory table: sku (TEXT PRIMARY KEY), qty. It already has one row: ('A1', 10).

Your task: insert ('A1', 3) - since A1 already exists, instead add 3 to its existing qty rather than crashing or overwriting it - and return the row's final sku and qty.

You'll practice:

Writing INSERT ... ON CONFLICT (col) DO UPDATE SET ...
Referencing the attempted row's values with EXCLUDED

-- A1 already exists with qty 10. Add 3 more to it in one statement -
-- insert if missing, update (adding to the existing qty) if present.
INSERT INTO inventory (sku, qty) VALUES ('A1', 3);