skip to content

In SQLite, how does INSERT ... ON CONFLICT DO UPDATE resolve a conflict, and what does a RETURNING clause report for it?

level: middleimportance: should knowfreq 42%

answer

  1. borrowed from PostgreSQL
  2. only uniqueness conflicts count
  3. the excluded. qualifier
  4. always ABORT inside DO UPDATE
  5. rows in no particular order

basics

~20 s

SQLite's UPSERT (3.24.0) turns an INSERT that would break a UNIQUE or PRIMARY KEY constraint into DO NOTHING or an UPDATE of that row, with excluded.col holding the attempted value. RETURNING (3.35.0) reports each inserted or updated row, in arbitrary order.

solid answer

~40 s

In SQLite, `INSERT ... ON CONFLICT(target) DO UPDATE SET ...` (since 3.24.0, PostgreSQL-style) applies when an inserted row would violate the uniqueness constraint named by the target: a PRIMARY KEY, a UNIQUE constraint or a unique index. Instead of failing, it updates only the conflicting row. Bare column names mean the stored values, `excluded.col` means the value you tried to insert, and an optional `WHERE` can turn the update into a no-op. It does not intercept NOT NULL, CHECK or foreign key failures, and a constraint failure inside DO UPDATE always aborts the whole statement. `RETURNING` (3.35.0) emits one row per inserted or updated row, with post-change values, but in arbitrary order and without changes made later by AFTER triggers. So return the natural key with the id rather than matching rows by position.

code

sql · 9 lines
sql
CREATE TABLE products(
  id    INTEGER PRIMARY KEY,
  sku   TEXT NOT NULL UNIQUE,
  price REAL,
  seen  INTEGER NOT NULL DEFAULT 1
);
INSERT INTO products(sku, price) VALUES ('A-100', 19.99), ('B-200', 5.00)
  ON CONFLICT(sku) DO UPDATE SET price = excluded.price, seen = seen + 1
  RETURNING id, sku, price, seen;

go deeper

for a junior

Recall that ON CONFLICT DO NOTHING or DO UPDATE turns a duplicate-key insert into a skip or an update, and that excluded.col holds the attempted value.

for a middle

Explain the conflict target, what UPSERT does not catch, the always-ABORT rule in DO UPDATE, how it differs from INSERT OR REPLACE, and RETURNING's order and trigger caveats.

for a senior

Design a re-runnable import with upsert and RETURNING that survives bad rows, maps ids by key, and still works on the oldest SQLite your devices ship.

for a principal

Set the team rule for writes that may hit existing rows: upsert over REPLACE, how RETURNING is relied on, and the minimum SQLite version that implies.

## UPSERT in SQLite UPSERT is not standard SQL (the standard's MERGE statement is a separate subject). SQLite added it in **3.24.0**, following PostgreSQL's syntax with some generalisations. An upsert is an ordinary INSERT followed by one or more `ON CONFLICT` clauses: - The **conflict target**, the column list (with an optional WHERE) between `ON CONFLICT` and `DO`, names the uniqueness constraint to watch: a PRIMARY KEY, a UNIQUE constraint or a unique index. If no such constraint matches, preparing the statement fails with `ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint`. - **`DO NOTHING`** skips the conflicting row. **`DO UPDATE SET ...`** updates the one existing row that caused the conflict, so it needs no WHERE to find that row. - Inside DO UPDATE, a bare column name means the value **already stored**, and **`excluded.col`** means the value the INSERT tried to write. SQLite accepts both `count+1` and `vocabulary.count+1`; PostgreSQL requires the qualified form. - An optional `WHERE` after the SET list makes the update conditional, for example only when `excluded.updated_at > products.updated_at`. - In a multi-row INSERT, the decision is made separately for each row. - Since **3.35.0** an INSERT may carry several `ON CONFLICT` clauses, checked in order, and the last one may omit its target to catch any other uniqueness failure. Only the first matching clause runs for a row. ## What UPSERT does not catch 1. **Only uniqueness conflicts.** A failed NOT NULL, CHECK or foreign key constraint, or a constraint enforced by a trigger, is not intercepted; the INSERT fails as usual. 2. **DO UPDATE always uses ABORT.** If the update itself violates a constraint, for example by setting a NOT NULL column to NULL, the whole INSERT statement is rolled back and stops, even if it was written `INSERT OR IGNORE` or runs in a trigger with another conflict policy. 3. **Virtual tables** do not support UPSERT. 4. **A parsing trap.** In `INSERT INTO t1 SELECT * FROM t2 ON CONFLICT ...`, the parser cannot tell whether `ON` begins the upsert or a join condition. Add a WHERE clause, even `WHERE true`, before `ON CONFLICT`. ## UPSERT versus INSERT OR REPLACE | | `ON CONFLICT ... DO UPDATE` | `INSERT OR REPLACE` | |---|---|---| | Existing row | updated in place | deleted, then the new row inserted | | Columns not supplied | keep their values | revert to their defaults | | Rowid | unchanged | reassigned unless supplied | | Delete triggers | do not fire | fire only with recursive triggers enabled | | NULL in a NOT NULL column | fails the statement (ABORT by default) | replaced by the column default if one exists | REPLACE is older and shorter, but it destroys data that the INSERT did not mention, which is why an upsert is usually the right tool for re-runnable imports. ## RETURNING in SQLite `RETURNING` arrived in **3.35.0**, modelled on PostgreSQL and also outside the standard. Added to a top-level INSERT, UPDATE or DELETE, it emits one result row per row changed. For an upsert it reports **both inserted and updated rows**; a row skipped by DO NOTHING is not changed, so it produces no output. Column references give the value after the change for INSERT and UPDATE, and before it for DELETE. SQLite's own caveats are what interviewers probe: - **Order is arbitrary.** The output rows need not follow the VALUES order or the processing order, and the order can change between releases and runs. Return a natural key such as `sku` alongside `id`, instead of matching output to input by position. - **Output is held in memory.** All the changes happen during the first `sqlite3_step()` call, and the RETURNING rows are buffered until they are done, so returning many rows or large values costs temporary memory. - **Triggers are invisible.** The values are the ones the top-level statement wrote. Changes that AFTER triggers make later, and rows changed through foreign key actions or triggers, are not reported. - **Top level only.** RETURNING cannot be used inside a trigger, and the statement cannot serve as a subquery or a common table expression, which PostgreSQL allows. - **Expressions are limited.** No top-level aggregate or window functions, and only the table being modified may be referenced (not the extra tables in an `UPDATE ... FROM`). ## On a phone A device's built-in SQLite can be older than the current release. Before 3.24.0 there is no UPSERT; before 3.35.0 there is no RETURNING and no multiple or target-less ON CONFLICT clauses. Check `sqlite_version()`, and fall back to `last_insert_rowid()` and `changes()` after plain statements where RETURNING is missing.

  • A SQLite upsert's DO UPDATE sets a NOT NULL column to NULL for one of 500 rows. What happens?
    The conflict resolution inside DO UPDATE is always ABORT, so that one violation rolls back the entire INSERT statement, all 500 rows, and returns the constraint error. Writing `INSERT OR IGNORE` does not change it. Validate or COALESCE the incoming values, or split the batch, if one bad row must not sink the rest.
  • Why should code not match SQLite RETURNING rows to its input list by position?
    SQLite documents the RETURNING output order as arbitrary: it is not tied to the VALUES order or to the order rows were processed, and it can change with the schema, the release or between runs. Return a natural key, such as `sku`, with the generated `id`, and match on that key.

  • ON CONFLICT DO NOTHING also skips rows that fail a CHECK constraint
  • INSERT OR REPLACE is the same as an upsert in SQLite
  • RETURNING rows come back in the order of the VALUES list
  • RETURNING shows values changed by AFTER triggers
  • In DO UPDATE, a bare column name means the value being inserted

Assumes SQLite 3.53; UPSERT exists from 3.24.0, while RETURNING and multiple or target-less ON CONFLICT clauses need 3.35.0.