Upserts: Insert or Update

An upsert inserts a row, or updates it if it already exists — for example, syncing product data from a supplier, or counting page views.

ON CONFLICT (SQLite and PostgreSQL)

-- standalone
CREATE TABLE page_views (
  page  TEXT PRIMARY KEY,
  views INTEGER NOT NULL DEFAULT 0
);

INSERT INTO page_views (page, views) VALUES ('/home', 1)
ON CONFLICT (page) DO UPDATE SET views = views + 1;

INSERT INTO page_views (page, views) VALUES ('/home', 1)
ON CONFLICT (page) DO UPDATE SET views = views + 1;

INSERT INTO page_views (page, views) VALUES ('/pricing', 1)
ON CONFLICT (page) DO UPDATE SET views = views + 1;

SELECT * FROM page_views ORDER BY page;

+----------+-------+
|   page   | views |
+----------+-------+
| /home    |     2 |
| /pricing |     1 |
+----------+-------+

• ON CONFLICT (page) names the unique column (or constraint) that detects "already exists".
• DO UPDATE SET ... says what to change instead. excluded.column refers to the value you tried to insert: DO UPDATE SET price = excluded.price.
• DO NOTHING skips rows that already exist.

Syncing data

INSERT INTO products (id, name, category, price) VALUES
  (2, 'USB-C Hub', 'Accessories', 32.00),
  (7, 'Desk Mat', 'Accessories', 19.99)
ON CONFLICT (id) DO UPDATE SET price = excluded.price;

SELECT id, name, price FROM products WHERE id IN (2, 7);

+----+-----------+-------+
| id |   name    | price |
+----+-----------+-------+
|  2 | USB-C Hub |    32 |
|  7 | Desk Mat  | 19.99 |
+----+-----------+-------+

Product 2's price was updated; product 7 was inserted.

Other databases

• MySQL: INSERT ... ON DUPLICATE KEY UPDATE price = VALUES(price) (or a row alias in newer versions).
• SQL Server, Oracle, PostgreSQL 15+: the standard MERGE statement.

An upsert is atomic: unlike "check if it exists, then insert or update" in application code, two simultaneous requests can't both insert the same row.