Upsert and Conflict Handling

The OR-conflict clauses

INSERT and UPDATE accept an OR <resolution> clause that decides what happens when a constraint is violated. This is the older, coarser mechanism.

INSERT OR IGNORE INTO tags(name) VALUES('sql');
INSERT OR REPLACE INTO settings(k, v) VALUES('theme', 'dark');
UPDATE OR IGNORE users SET email = ? WHERE id = ?;
REPLACE INTO settings(k, v) VALUES('theme','dark');   -- alias for INSERT OR REPLACE

OR FAIL versus OR ABORT is the subtle pair. Both stop the statement, but FAIL leaves earlier rows of a multi-row insert in place while ABORT undoes them.

CREATE TABLE t(a INTEGER PRIMARY KEY);
INSERT OR FAIL INTO t VALUES(1),(2),(1),(3);
-- Error: UNIQUE constraint failed: t.a
SELECT count(*) FROM t;    -- 2, rows 1 and 2 survived

DELETE FROM t;
INSERT OR ABORT INTO t VALUES(1),(2),(1),(3);
-- Error: UNIQUE constraint failed: t.a
SELECT count(*) FROM t;    -- 0

OR IGNORE also swallows a NOT NULL or CHECK failure, not just a uniqueness one, so it can silently drop rows for a reason you did not intend. Name the conflict you mean with ON CONFLICT instead.

Why INSERT OR REPLACE is dangerous

REPLACE is a delete followed by an insert, not an update. Three consequences, all of which bite in production.

CREATE TABLE users(
  id INTEGER PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  name TEXT,
  created_at TEXT DEFAULT 'orig'
) STRICT;
INSERT INTO users(email, name) VALUES('a@b.c', 'Ada');
SELECT * FROM users;         -- 1|a@b.c|Ada|orig

-- Meant to update the name. Did not mention the other columns.
INSERT OR REPLACE INTO users(email, name) VALUES('a@b.c', 'Ada L');
SELECT * FROM users;         -- 2|a@b.c|Ada L|orig

The row is gone and a new one took its place. id changed from 1 to 2, so every foreign key pointing at it is now dangling or cascade-deleted. created_at did not carry over either, it was recomputed from the column DEFAULT, which here happens to produce the same text. A column with no DEFAULT would come back NULL.

1. Unset columns are lost, replaced by the column's DEFAULT or NULL. 2. The rowid changes, and with PRAGMA foreign_keys=ON plus ON DELETE CASCADE the delete half cascades and destroys child rows. 3. DELETE triggers fire, unless PRAGMA recursive_triggers is off, which is where the behavior varies. INSERT triggers always fire.

-- REPLACE can delete MULTIPLE rows, one per violated unique constraint
CREATE TABLE m(a INTEGER UNIQUE, b INTEGER UNIQUE) STRICT;
INSERT INTO m VALUES(1,10),(2,20);
INSERT OR REPLACE INTO m VALUES(1,20);
SELECT * FROM m;    -- 1|20   both original rows were removed

Do not use INSERT OR REPLACE for an upsert. Use ON CONFLICT ... DO UPDATE, which is a real update: the rowid is stable, unnamed columns keep their values, no delete trigger fires, and no child row cascades away. REPLACE is only appropriate when you genuinely want the whole row overwritten, for example a pure key-value cache table.

UPSERT with ON CONFLICT

UPSERT arrived in 3.24, with the syntax borrowed from Postgres. The excluded pseudo-table holds the row you tried to insert, and the table name refers to the row already stored.

CREATE TABLE counters(
  key TEXT PRIMARY KEY,
  n INTEGER NOT NULL DEFAULT 0,
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT;

INSERT INTO counters(key, n) VALUES('hits', 1)
ON CONFLICT(key) DO UPDATE SET
  n = counters.n + excluded.n,
  updated_at = datetime('now');

DO NOTHING is the other action. Unlike INSERT OR IGNORE, it only ignores the conflict you named and still raises on any other constraint.

INSERT INTO tags(name) VALUES('sql') ON CONFLICT(name) DO NOTHING;
INSERT INTO tags(name) VALUES('sql') ON CONFLICT DO NOTHING;   -- any conflict, 3.35+

The bare ON CONFLICT DO NOTHING with no target needs 3.35+ and, like INSERT OR IGNORE, swallows every constraint violation including a CHECK failure. Naming the target is the safer default.

Conflict targets

The target must match a UNIQUE constraint, a PRIMARY KEY, or a unique index, by its column list or by the exact expression it indexes.

CREATE UNIQUE INDEX users_lower_email ON users(lower(email));
INSERT INTO users(email, name) VALUES('ADA@b.c', 'Ada')
ON CONFLICT(lower(email)) DO UPDATE SET name = excluded.name;

CREATE UNIQUE INDEX u_live ON users(email) WHERE deleted_at IS NULL;
INSERT INTO users(email) VALUES('a@b.c')
ON CONFLICT(email) WHERE deleted_at IS NULL DO NOTHING;

Two errors to recognize:

Error: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint

The column list does not correspond to a unique index. Check the exact columns, and remember that a PRIMARY KEY on a WITHOUT ROWID table counts while a plain INTEGER PRIMARY KEY is matched as ON CONFLICT(id).

Error: near "DO": syntax error

Almost always a missing WHERE before the ON CONFLICT in an INSERT ... SELECT, see the parsing gotcha below.

Foreign key and NOT NULL violations are not conflict targets. ON CONFLICT only covers uniqueness, so a foreign key failure still aborts the statement.

Two WHERE clauses, and a parsing gotcha

There are up to three WHERE clauses in a full UPSERT, and they mean different things.

INSERT INTO device_state(device_id, seq, payload)
SELECT device_id, seq, payload FROM incoming
WHERE seq > 0                              -- 1. filters the SELECT source
ON CONFLICT(device_id) WHERE active = 1    -- 2. picks a partial unique index
DO UPDATE SET seq = excluded.seq, payload = excluded.payload
WHERE device_state.seq < excluded.seq;     -- 3. makes the update conditional

Clause 3 is the useful one. When it is false the existing row is left completely alone, no update happens, and RETURNING yields nothing for that row. That gives you a monotonic write with no read-modify-write race.

The gotcha: with INSERT ... SELECT ... ON CONFLICT, the parser cannot tell whether ON begins a join or the conflict clause, so a SELECT with no WHERE is a syntax error. The documented workaround is a dummy WHERE true.

-- Syntax error
INSERT INTO t(a,b) SELECT a,b FROM s ON CONFLICT(a) DO NOTHING;
-- Fine
INSERT INTO t(a,b) SELECT a,b FROM s WHERE true ON CONFLICT(a) DO NOTHING;

You may also chain multiple ON CONFLICT clauses (3.35+), evaluated in order, with only the last one allowed to omit its target.

INSERT INTO users(id, email, name) VALUES(1, 'a@b.c', 'Ada')
ON CONFLICT(id)    DO UPDATE SET name = excluded.name
ON CONFLICT(email) DO NOTHING;

RETURNING

RETURNING (3.35+) works on INSERT, UPDATE, DELETE, and UPSERT, and it reads the row after defaults, generated columns, and triggers have applied.

INSERT INTO users(email, name) VALUES('a@b.c','Ada')
RETURNING id, created_at;

INSERT INTO counters(key, n) VALUES('hits', 1)
ON CONFLICT(key) DO UPDATE SET n = counters.n + 1
RETURNING key, n;

UPDATE posts SET status='published', published_at=datetime('now')
WHERE id = ? AND status = 'draft'
RETURNING id, status, published_at;

DELETE FROM sessions WHERE expires_at < datetime('now')
RETURNING id, user_id;

UPDATE ... RETURNING returning zero rows is how you detect "not found, or not in the expected state" without a second query. That is the cheapest optimistic concurrency control available.

Limitations, all documented:

• Output order is undefined, and ORDER BY is not allowed on RETURNING.
• It cannot be used on a virtual table, and it does not fire for rows a trigger changed.
• It cannot contain a subquery, an aggregate, or a window function.
• Rows come back as the statement runs, so a RETURNING on a large DELETE streams.

Before 3.35, the fallback for a single insert is last_insert_rowid(), which returns the rowid of the most recent successful insert on that connection.

INSERT INTO users(email) VALUES('a@b.c');
SELECT last_insert_rowid();

Note that last_insert_rowid() is per connection, is not updated by an INSERT OR IGNORE that skipped, is not reset by a rollback, and is meaningless on a WITHOUT ROWID table.

Did it insert or update?

DO UPDATE returns a row either way and does not say which branch ran. There is no xmax trick here, so compare a timestamp or count the changes.

-- Timestamp comparison, the portable way
INSERT INTO counters(key, n) VALUES('hits', 1)
ON CONFLICT(key) DO UPDATE SET n = counters.n + 1, updated_at = datetime('now')
RETURNING key, n, (updated_at = created_at) AS was_insert;

changes() gives the rows affected by the most recent statement, which is 1 for both branches of an upsert, and 0 when a conditional DO UPDATE declined.

INSERT INTO counters(key,n) VALUES('hits',1)
ON CONFLICT(key) DO UPDATE SET n=counters.n+1 WHERE counters.n < 10;
SELECT changes();    -- 1 when it inserted or updated, 0 when the WHERE declined

Useful idioms:

-- Insert-or-fetch the id, in one round trip
INSERT INTO tags(name) VALUES(?)
ON CONFLICT(name) DO UPDATE SET name = name   -- a no-op update, so RETURNING fires
RETURNING id;

-- Bulk merge from a staging table, monotonic
INSERT INTO state(k, seq, v)
SELECT k, seq, v FROM staging WHERE true
ON CONFLICT(k) DO UPDATE SET seq = excluded.seq, v = excluded.v
WHERE state.seq < excluded.seq;

-- Counter with a floor and a ceiling
INSERT INTO counters(key, n) VALUES(?, 1)
ON CONFLICT(key) DO UPDATE SET n = min(counters.n + 1, 100);

The DO UPDATE SET name = name trick exists because DO NOTHING produces no RETURNING row, so there is no way to learn the existing id from it. A no-op update counts as an update and hands the row back.

Transactions around all of this

Conflict handling only makes sense in the context of the transaction it runs in. SQLite is autocommit by default, so each statement is its own transaction unless you open one.

BEGIN;                       -- DEFERRED, the default. Takes no lock yet
BEGIN IMMEDIATE;             -- take the write lock now, avoids a later SQLITE_BUSY
BEGIN EXCLUSIVE;             -- block other readers too, rarely needed under WAL
  INSERT ...;
  UPDATE ...;
SAVEPOINT s1;
  DELETE ...;
ROLLBACK TO s1;              -- undo to the savepoint, transaction still open
RELEASE s1;
COMMIT;
ROLLBACK;                    -- undo everything

Use BEGIN IMMEDIATE for any transaction that will write. A DEFERRED transaction takes a read lock on its first read and only tries to upgrade on its first write, and if another writer got there first the upgrade fails with SQLITE_BUSY that busy_timeout cannot wait out, because waiting would deadlock. IMMEDIATE takes the write lock up front, where busy_timeout does apply.

OR ROLLBACK interacts badly with an application-managed transaction, since it discards work the caller thought was still pending. Prefer OR ABORT (the default) and handle the error yourself.

For a bulk load, one transaction around thousands of inserts is the single biggest speedup available, often two orders of magnitude, because each autocommit statement otherwise pays a durability fsync.

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
BEGIN IMMEDIATE;
-- thousands of prepared-statement inserts here
COMMIT;