UPSERT: Insert or Update in One Statement

This lesson covers UPSERT — doing "insert if missing, update if present" in one statement — for efficient data registration. It's for anyone searching "SQL UPSERT tutorial."

INSERT ... ON CONFLICT DO UPDATE performs "insert if it doesn't exist, update if it does" as a single statement (this pattern is called UPSERT) — handy for something like restocking inventory, where you want to add to an existing quantity.

The example tries to insert a product with id = 1, which already exists, and ON CONFLICT(id) DO UPDATE SET stock = stock + excluded.stock; handles the conflict by adding to the existing stock instead. excluded is a special keyword referring to "the value that was just about to be inserted."

A common early mistake is forgetting that the column named in ON CONFLICT (here, id) needs a uniqueness constraint like PRIMARY KEY already defined — without it, there's no way for a "conflict" to even be detected. Doing this in one step, rather than "check if it exists, then decide whether to insert or update," is not just more concise — it also closes the window where another process could sneak in a change between your check and your action.