UPSERT
UPSERT is a database operation that inserts a new row or updates an existing one if a matching row already exists, typically expressed with an ON CONFLICT clause.
Overview
"Upsert" (update + insert) solves the common problem of writing a batch of records where some rows are new and some already exist and need refreshing — without first checking which is which. Most modern SQL engines implement it via INSERT ... ON CONFLICT, based on a unique key or primary key:
Copy code
INSERT INTO customers (id, name, email)
VALUES (1, 'Ada Lovelace', 'ada@example.com')
ON CONFLICT (id) DO UPDATE SET
name = EXCLUDED.name,
email = EXCLUDED.email;
EXCLUDED refers to the row that was proposed for insertion but conflicted, letting you reference its values inside the DO UPDATE SET clause. ON CONFLICT (id) DO NOTHING instead silently skips rows that already exist, without erroring or overwriting.
DuckDB specifics
DuckDB implements the Postgres-style INSERT INTO ... ON CONFLICT (columns) DO UPDATE SET ... / DO NOTHING syntax, requiring a primary key or unique constraint on the conflict target column(s):
Copy code
CREATE TABLE customers (id INTEGER PRIMARY KEY, name VARCHAR, updated_at TIMESTAMP);
INSERT INTO customers VALUES (1, 'Ada Lovelace', now())
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, updated_at = EXCLUDED.updated_at;
As of DuckDB 1.4, DuckDB also supports the SQL-standard MERGE INTO statement as a more flexible alternative for upserts — it can match on any custom condition rather than requiring a declared primary key, and can express insert, update, and delete actions in one statement:
Copy code
MERGE INTO customers
USING staged_customers s ON customers.id = s.id
WHEN MATCHED THEN UPDATE SET name = s.name
WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.name, s.updated_at);
Related terms
The UPDATE statement modifies existing rows in a table, setting one or more column values for rows that match an optional WHERE condition.
INSERT statement →The INSERT statement is a fundamental SQL command used to add new rows of data into a table.
primary key →A primary key uniquely identifies each database row. Learn SQL definitions, examples, UUID vs auto-increment best practices, and how to fix constraint errors.
MERGE statement →MERGE INTO is a SQL statement that inserts, updates, or deletes rows in a target table based on how they match rows from a source table, in a single atomic operation.
UNIQUE constraint →A UNIQUE constraint ensures that all values in a column, or combination of columns, are distinct across every row in a table.
Referential integrity →Referential integrity is the guarantee that a reference from one table to another — typically a foreign key — always points to a row that actually exists, preventing orphaned records.
FAQS
DuckDB supports INSERT INTO ... ON CONFLICT (key_columns) DO UPDATE SET ... (or DO NOTHING), matching PostgreSQL syntax. As of DuckDB 1.4, the more general MERGE INTO statement is also available for upserts that don't rely on a primary key.
EXCLUDED refers to the row values that were proposed by the INSERT but triggered the conflict, letting the DO UPDATE SET clause reference the incoming values (e.g., EXCLUDED.name) rather than the existing row's values.
