UPSERT: INSERT … ON CONFLICT

Insert, or update if it already exists — atomically.

Insert-or-update in one statement

You often want "add this row, but if it already exists, update it instead". Postgres does it atomically with ON CONFLICT:

INSERT INTO books (id, title, author_id, genre, price, stock)
VALUES (1, 'The Glass Forest', 1, 'fiction', 14.99, 20)
ON CONFLICT (id) DO UPDATE
  SET stock = EXCLUDED.stock
RETURNING id, stock;

Book 1 already exists, so the conflict on the id primary key fires the DO UPDATE. EXCLUDED refers to the row you tried to insert — so this restocks book 1 to 20. ON CONFLICT ... DO NOTHING is the other common form.

The same statement covers both worlds, and Postgres decides atomically — no read-then-write race between the check and the write. Book 1 does exist, so the right-hand branch is the one that runs and its stock moves from 12 to 20.

EXCLUDED is the row you proposed, not the row on disk. DO UPDATE SET stock = EXCLUDED.stock moves stock alone; every other column keeps what the table already held, even though the VALUES list supplied one.