Idempotency makes repeating an operation produce the same effective result as running it once. In SQL, a repeated insert can fail on a primary-key conflict, so conflict handling makes the statement repeatable—but not necessarily correct. section 3.4.1
Use the conflict strategy that matches the intended workflow:
- Use ON CONFLICT DO NOTHING to suppress a duplicate primary-key error. section 3.4.1
- Avoid explicit generated IDs and name inserted columns so repeats do not depend on fragile column order. section 3.4.1
- Use ON CONFLICT on a business key with DO UPDATE when newer data should replace the existing value. section 3.4.1
- INSTALL and LOAD commands for DuckDB extensions are themselves idempotent and can be issued repeatedly. section 2.4
Repeatable price loading
INSERT INTO prices(value, valid_from, valid_until) VALUES (11.47, '2019-01-01', '2019-02-01') ON CONFLICT (valid_from) DO UPDATE SET value = excluded.value; section 3.4.1
INSERT INTO prices VALUES (1, 11.59, '2018-12-01', '2019-01-01') ON CONFLICT DO NOTHING;
Inspect the minimal repeatable insert that ignores an existing conflict, then compare it with update-based handling. section 3.4.1
