Upsert a row (INSERT or UPDATE in one statement)

Upsert a row (INSERT or UPDATE in one statement) snippet

An upsert inserts the row if it does not exist and updates it if it does — in ONE statement, no read-then-write race.

An upsert inserts the row if it does not exist and updates it if it does — in ONE statement, no read-then-write race. The naive two-step (SELECT, then INSERT or UPDATE) corrupts data under concurrency: two writers both miss the SELECT and both insert. ON CONFLICT is the Postgres answer; MySQL spells it ON DUPLICATE KEY UPDATE and SQLite copies the Postgres syntax. Two things decide correctness: the conflict target must be a real unique constraint or primary key, and DO UPDATE needs the excluded.* pseudo-table to read the values that failed to insert.

Runnable recipe · 1 languages

Files & Datasqlupsertinserton-conflictpostgres

Every language

1 implementations, copy-ready. One at a time with syntax highlighting, or all inline.

SQLSQLrunnable

INSERT INTO users (id, email, name)
VALUES (42, 'ada@example.com', 'Ada')
ON CONFLICT (id) DO UPDATE
    SET email = excluded.email,
        name  = excluded.name
RETURNING id, (xmax = 0) AS inserted_this_time;

excluded holds the row that failed to insert — read the challenger's values from it, not from the table. ON CONFLICT DO NOTHING is the ignore-duplicates variant. Run it twice in the playground: first pass inserts (true), second pass updates (false).

Run in the SQL playground →

Keep going

Read the database-objects cheatsheet →