SQLite UPSERT makes conflict handling part of INSERT

An insert often arrives with a rule for an existing row. A counter should increase if its key already exists. A newer record should replace selected values while an older record should leave them alone. Expressing that rule through a preliminary SELECT makes application code responsible for coordinating the observation with the eventual write. SQLite 3.24.0 lets the INSERT statement describe the conflict and its resolution together.

The 4 June 2018 release adds PostgreSQL-style UPSERT. Its ON CONFLICT clause identifies a uniqueness constraint, then selects DO NOTHING or DO UPDATE when inserting a row would violate it. The result is a more precise vocabulary for deciding what an incoming row means when an identity already exists.

I appreciate this addition because it keeps an application's mutation rule close to the constraint that makes the rule necessary. SQLite remains an embedded database. The application gains a better statement without needing a separate coordination service or a custom convention for every caller.

The constraint identifies the existing row

Consider an illustrative table that counts observations for named keys. The key determines which observations belong to the same counter, and a default supplies the first count.

CREATE TABLE observations(
  name TEXT PRIMARY KEY,
  count INTEGER NOT NULL DEFAULT 1
);

INSERT INTO observations(name) VALUES('ready')
ON CONFLICT(name) DO UPDATE SET count=count+1;

The first occurrence inserts a row with a count of one. An attempted insert of the same name updates the existing count. The expression reads the existing value of count, so the application does not need to fetch that value and send back an incremented number.

This is the same mechanism exercised in the release's UPSERT tests. Those tests insert repeated keys and increment an existing column in the update action. They also demonstrate that conflict handling takes place separately for rows in a multi-row INSERT. A batch can add a new key and update an existing key in the same statement.

The conflict target must match a primary key or unique constraint. Listing a column that happens to contain unique values is insufficient. The tests explicitly reject a target that has no matching constraint. The database needs a declared rule it can use to establish which conflict the clause handles.

That connection between statement and schema is useful documentation. A reader can look at ON CONFLICT(name) and find the uniqueness rule that gives the update its identity. If a schema change removes or changes that rule, the statement's assumptions become visible at preparation or execution instead of remaining an undocumented property of current data.

Existing values and proposed values have different names

An update can also use the values from the attempted insert. SQLite exposes those through the special excluded qualifier. This distinguishes a value already stored in the target row from the value proposed by the incoming row.

A table containing a generation number makes the distinction concrete. The following example preserves an existing row unless an incoming generation is greater.

CREATE TABLE documents(
  id INTEGER PRIMARY KEY,
  generation INTEGER NOT NULL,
  body TEXT NOT NULL
);

INSERT INTO documents(id, generation, body)
VALUES(7, 4, 'revised text')
ON CONFLICT(id) DO UPDATE SET
  generation=excluded.generation,
  body=excluded.body
WHERE excluded.generation > documents.generation;

For a new identifier, SQLite inserts the proposed row. If the identifier exists, the WHERE clause compares the proposed generation with the stored generation. Only a greater generation causes the assignments to run. An equal or smaller generation leaves the existing row unchanged.

The conditional-update tests in the 3.24.0 tree cover this structure directly. They combine an assignment from excluded, an increment of an existing value and a WHERE comparison between the two rows. Their expected results include an inserted row, an updated row and an existing row that remains unchanged.

The final WHERE does not find an arbitrary set of rows to update. The uniqueness conflict has already identified the candidate row. Its purpose is to decide whether the update action applies to that row. This makes a conditional UPSERT suitable for rules that accept some repeated identities and reject others without treating every repetition as a SQL error.

The example's generation policy is an application choice. SQLite does not establish that a larger generation contains more authoritative data. It executes the comparison supplied by the statement. Putting that policy into SQL makes it explicit, but the producer and consumer still have to agree on what the generation represents.

Ignoring a conflict can be equally specific

Some inserts should preserve the first value. DO NOTHING expresses that choice without inventing an update that assigns every column to itself. With a target, it applies to the specified uniqueness rule. The tests also show ON CONFLICT DO NOTHING without a target.

Target selection matters when a table has several unique constraints. The release tests create a separate unique index and show that handling one key does not automatically handle a conflict with another. A statement that names the primary key can still fail when a different unique index rejects its proposed values.

That is a useful property when duplicate identities are routine but duplicate alternate identifiers indicate bad input. Ignoring every uniqueness conflict can hide the latter problem. A targeted clause documents which collision the application accepts as part of ordinary operation.

The tests also cover partial unique indexes. Their target includes a WHERE expression corresponding to the index condition. A similar-looking condition that does not identify the required index is rejected. The target describes an existing uniqueness mechanism, so it is not simply an arbitrary Boolean filter over incoming data.

These details make UPSERT a schema-dependent operation. A generic helper that accepts only a table name and a list of assignments would miss some of the information required to construct it correctly. The caller also needs to identify the constraint and distinguish stored values from incoming values.

A further batch test proposes the same key several times and records successive conditional updates. The final value reflects those accepted updates within the statement. A batch is therefore not a collection of independent comparisons against one frozen pre-insert copy of the table. Earlier accepted rows can affect what a later proposed row encounters.

Triggers observe an insert attempt and its resolution

One subtle part of this feature appears in the trigger tests. On an UPSERT that executes its update action, a BEFORE INSERT trigger runs, followed by the update triggers. The expected record contains the before-insert event and both before-update and after-update events.

For DO NOTHING, the test records only the BEFORE INSERT trigger. A conditional DO UPDATE whose WHERE is false also records that initial trigger without an update event. Describing the final row as unchanged therefore does not mean that no SQL inside a trigger executed.

This distinction matters when trigger bodies maintain an audit table or perform other database work. A trigger attached to the attempted insertion and a trigger attached to a successful update describe different events. Their names and purposes need to match the stage they observe.

The test suite repeats these cases for a WITHOUT ROWID table. The documented behavior is not limited to the common rowid-table representation. That matters because UPSERT follows the declared identity and update rules even when the table's storage organization differs.

I find these trigger cases more informative than a minimal syntax example. They show where the engine places the new action within existing INSERT processing. Applications with triggers can reason about the event sequence using actual versioned tests instead of assuming that an UPSERT behaves exactly like whichever final operation it resembles.

Updating a row need not rewrite unchanged pages

The same release improves low-level UPDATE behavior. Its notes say SQLite avoids extra disk writes when an update leaves the stored content unchanged. For a record spanning multiple pages, only pages whose content changes need to be written.

That optimization does not suppress triggers or otherwise change higher-level SQL behavior. It concerns disk I/O after the engine has determined the update's contents. A statement can still execute its update semantics even when the resulting bytes equal the original bytes.

This is separate from a conditional UPSERT whose WHERE prevents the update action. A false condition chooses no update. An update that writes equal values still performs the higher-level operation but may avoid unnecessary physical writes. Confusing the two would make trigger behavior difficult to explain.

For me, the practical gain in 3.24.0 is the ability to express these distinctions in one statement. The conflict target establishes identity, excluded names the proposed values, and the update condition decides whether the existing row should change. The release tests give concrete examples of each decision, including the trigger events that remain visible when the attempted insertion does not add a row.