How deeply does DuckDB in Action cover ON CONFLICT clause?

Explains default and named conflict targets, DO NOTHING, DO UPDATE, excluded values, and computed update expressions.

ON CONFLICT clause

An ON CONFLICT clause specifies how an insert or update handles a uniqueness or related constraint conflict.

ON CONFLICT makes inserts resilient when a row violates a primary-key or other uniqueness constraint. Choose whether to ignore the incoming row or merge its values into the existing row. section 3.4.1 section 3.4.2

Use the clause according to the desired data workflow:

Use ON CONFLICT DO NOTHING to make a repeated insert harmless; by default, it targets the primary index. section 3.4.1
Name a conflict target, such as valid_from or (system_id, read_on), when a specific uniqueness rule should trigger the action. section 3.4.1 section 3.4.2
Use DO UPDATE to replace or combine existing values; expressions and CASE statements are allowed. section 3.4.2
Prefer explicit columns and generated IDs rather than suppressing errors caused by fragile positional inserts. section 3.4.1

Average readings that share a device and timestamp

Insert a new reading, then merge a conflicting reading into the existing row: INSERT INTO readings(system_id, read_on, power) VALUES (10, '2023-06-05 13:00:00', 3000) ON CONFLICT(system_id, read_on) DO UPDATE SET power = CASE WHEN power = 0 THEN excluded.power ELSE (power + excluded.power) / 2 END; The conflict target uses the composed key, while excluded.power refers to the incoming value. section 3.4.2

Listing 3.8 Computing new values on conflict

INSERT INTO readings(system_id, read_on, power)
  VALUES (10, '2023-06-05 13:00:00', 4000);

INSERT INTO readings(system_id, read_on, power)
  VALUES (10, '2023-06-05 13:00:00', 3000)
ON CONFLICT(system_id, read_on) DO UPDATE  #1
SET power = CASE
  WHEN power = 0 THEN excluded.power  #2
  ELSE (power + excluded.power) / 2 END;

Inspect the complete two-step reading merge, including the initial insert and the conflict update. section 3.4.2

How DuckDB in Action covers it

liveBook manning.com

Read DuckDB in Action on liveBook Get the book at manning.com

Related concepts

race conditionaddresses · A race condition is a concurrency defect in which program behavior or results depend on the relative timing or ordering of interacting operations.
upsertimplements · An upsert is a data-modification operation that updates a matching record or resource, or inserts it when no match exists.
do nothing clauseused by · A DO NOTHING clause directs a conflicting INSERT operation to discard the conflicting row without raising an error or changing existing data.
insertuses · An insertion is an operation that adds an element, record, or key-value association to a collection or data store, with placement and replacement behavior determined by the target.
MERGE statementalternative to · A MERGE statement is an SQL data-modification command that conditionally inserts, updates, or deletes target rows based on source-to-target matches.

This explainer was generated by Manning's knowledge graph from the text of DuckDB in Action. The section links point to the parts of the book it draws on.