An upsert is a database operation that inserts a row if it doesn’t exist and updates it if it does, in a single statement. The name is a portmanteau of update and insert, and the operation exists because “write this record, whether or not I’ve seen it before” is one of the most common things an application or data pipeline needs to do.
Why not just insert or update?
Without upserts, you’d check whether the row exists, then branch: INSERT if it’s new, UPDATE if it’s not. That’s two round trips, and worse, it has a race condition — another process can insert the row between your check and your insert, and now your insert fails on a duplicate primary key. An upsert makes the whole thing one atomic statement: the database itself resolves the conflict.
The catch is that “does this row exist?” needs a definition. Upserts work against a unique constraint — usually the primary key — so the table has to have one for the database to detect the collision.
The syntax depends on your database
There’s no single upsert keyword, and the dialects differ enough to matter. PostgreSQL and SQLite use INSERT ... ON CONFLICT:
INSERT INTO inventory (sku, warehouse_id, quantity)
VALUES ('TSHIRT-M-BLUE', 3, 120)
ON CONFLICT (sku, warehouse_id)
DO UPDATE SET quantity = EXCLUDED.quantity;
If no row with that SKU and warehouse exists, it’s inserted. If one does, its quantity is overwritten — EXCLUDED refers to the values you tried to insert. You can also write DO NOTHING to silently skip duplicates.
MySQL’s equivalent is INSERT ... ON DUPLICATE KEY UPDATE, which triggers on any unique key violation rather than a named column list. The SQL standard’s answer is MERGE, which SQL Server, Oracle, Snowflake, BigQuery, and recent PostgreSQL versions support: it matches a source table against a target and lets you spell out what happens WHEN MATCHED and WHEN NOT MATCHED. MERGE is more verbose but more general — it can update, insert, and delete in one pass, which is why data warehouse loading jobs tend to use it.
Why upserts matter for analytics
You’ll rarely type an upsert into a BI tool — Metabase and other analytics tools read from your database; they issue queries, not writes. But upserts shape the data you analyze. ETL and sync tools use them to load records idempotently: run the same sync twice and you get updated rows, not duplicates. When a transactional database table or warehouse table is maintained by upserts, each entity appears once, with its latest state — which is exactly what you want before pointing a dashboard at it. If you’re instead seeing duplicate customers or double-counted orders in a chart, a missing upsert (or a missing unique key) upstream in the pipeline is one of the first places to look.
Related terms
Further reading
Put it to work
- Best practices for writing SQL queries — Tutorial
- PostgreSQL monitoring dashboard — Dashboard
