Upsert

Also called: insert or update, ON CONFLICT DO UPDATE, merge.

One database write that inserts a row if it isn't there yet, or updates it if it is, matched by a unique key. It avoids the race where two requests both check "not there" and both insert.

Save the same email twice. The upsert adds the row the first time and updates that row the second time. A plain INSERT fails on the unique email instead.

2 rows · 0 rows for ana@example.com

ana@example.com is not in the table yet.

Ready

Say it in a prompt

Import the product CSV with an upsert keyed by sku, using INSERT ... ON CONFLICT (sku) DO UPDATE to add new SKUs and update price and stock for existing ones. Write in batches of 500 rows and report how many rows were inserted and how many were updated.

Vague vs precise prompt

Vague prompt

save the user's settings

Typical resultReads the row, then inserts or updates in two separate steps. Two tabs saving at the same moment can create two settings rows, or one of them fails on a duplicate key.

Precise prompt

Save settings with an upsert on user_settings keyed by user_id: INSERT ... ON CONFLICT (user_id) DO UPDATE SET theme = EXCLUDED.theme, updated_at = now(), and keep the unique index on user_id.

Typical resultThere is always exactly one settings row per user, and two saves at the same moment simply keep the later change.

Seen on

PostgreSQL: INSERT ... ON CONFLICT DO UPDATE inserts or updates in one atomic step; the docs note it is also known as UPSERT.
Laravel: Eloquent's upsert method inserts or updates many records in one atomic operation, matched by the columns you name in uniqueBy.

You might describe it as

create it if it's new, update it if it's already there
save without getting a duplicate key error
add or replace the row in one step

Not to be confused with

Idempotent request
An upsert makes one database write safe to repeat; an idempotent request makes a whole API call safe to repeat.