SQL / INSERTING, UPDATING, AND DELETING ROWS
Upserts: inserting or updating in one step
Write INSERT ... ON CONFLICT so a row is inserted when its key is new and updated when it is not, without a race between checking and writing.
What you will learn
- Write INSERT ... ON CONFLICT (key) DO UPDATE to insert or update in one statement
- Name a unique or primary key column as the conflict target
- Use EXCLUDED.col for the incoming row and table.col for the stored row
- Guard DO UPDATE with WHERE to skip stale overwrites, or DO NOTHING to skip rows
Understanding Upserts: inserting or updating in one step
A row you want to store may or may not already exist, and usually you do not care which. Doing that as SELECT, then INSERT or UPDATE, has two problems: it costs an extra round trip, and between your SELECT and your INSERT another session can insert the same key, so you get a duplicate key error or an update that quietly overwrites work you never saw. INSERT ... ON CONFLICT closes that window by making the decision inside one statement, at the moment the unique index is consulted.
The mental model is a fallback, not a lookup. The server evaluates defaults, attempts the insert, and if that insert would violate the unique index you named as the conflict target, it updates the row already holding that key instead. Inside DO UPDATE two versions of the row are in scope: EXCLUDED is the row that was proposed and excluded from insertion, and the table name refers to the row already stored, which is why SET qty = inventory.qty + EXCLUDED.qty accumulates while SET qty = EXCLUDED.qty overwrites. You must name the conflict target because 'already exists' has no meaning until you say which uniqueness rule defines identity.
It stays an INSERT in every other respect. The reported count includes updated rows, update triggers fire on the row that was updated, and defaults such as a sequence are consumed before the conflict is known, so skipped rows leave gaps in generated ids. Two limits follow from the design: conflicts are detected only through unique or exclusion constraints, never through an arbitrary predicate, and one statement may update a given stored row at most once, so a batch that mentions the same key twice is an error rather than a double increment.
CREATE TABLE inventory (
sku text PRIMARY KEY,
name text NOT NULL,
qty integer NOT NULL
);
INSERT INTO inventory (sku, name, qty) VALUES
('A-100', 'Hex bolt', 40),
('B-200', 'Wing nut', 15);
-- one statement: A-100 already exists, C-300 does not
INSERT INTO inventory (sku, name, qty) VALUES
('A-100', 'Hex bolt', 25),
('C-300', 'Washer', 60)
ON CONFLICT (sku) DO UPDATE
SET qty = inventory.qty + EXCLUDED.qty;
SELECT sku, name, qty FROM inventory ORDER BY sku;An upsert is a single INSERT whose failure against one named unique constraint is turned into an UPDATE of the row that already holds that key.
Worked examples
Idempotent seeding with DO NOTHING
Loading reference rows that may already be there, without overwriting what is stored.
CREATE TABLE roles (
name text PRIMARY KEY,
level integer NOT NULL
);
INSERT INTO roles (name, level) VALUES ('admin', 3), ('viewer', 1);
INSERT INTO roles (name, level) VALUES ('admin', 9), ('editor', 2)
ON CONFLICT (name) DO NOTHING;
SELECT name, level FROM roles ORDER BY name;Example explained
Line 1ON CONFLICT (name) points at the primary key index, which is what decides that ('admin', 9) is a duplicate.
Line 2DO NOTHING discards the proposed row instead of raising a duplicate key error, so the stored level 3 survives.
Line 3The tag INSERT 0 1 counts only the row that reached the table, which is how you detect that one row was already present.
Line 4Because rerunning changes nothing, this is the usual shape for seed scripts and retried loaders.
Only overwrite with newer data
A WHERE clause on DO UPDATE keeps a stale incoming row from clobbering a fresher stored row.
CREATE TABLE doc (
id integer PRIMARY KEY,
body text NOT NULL,
version integer NOT NULL
);
INSERT INTO doc VALUES (1, 'final text', 7);
INSERT INTO doc (id, body, version)
VALUES (1, 'stale text', 4)
ON CONFLICT (id) DO UPDATE
SET body = EXCLUDED.body,
version = EXCLUDED.version
WHERE EXCLUDED.version > doc.version;
SELECT * FROM doc;Example explained
Line 1EXCLUDED.version is the 4 from the proposed row; doc.version is the 7 already on disk.
Line 2The WHERE is evaluated after the conflicting row has been located, so the update is skipped when it would move the row backwards.
Line 3INSERT 0 0 says nothing was inserted and nothing updated; a skipped conflict is not an error.
Line 4Without that WHERE the older body would have replaced the newer one and no part of the statement would have objected.
Duplicate keys inside one statement
Why a batch containing the same key twice aborts instead of applying both changes.
CREATE TABLE visits (
page text PRIMARY KEY,
hits integer NOT NULL
);
INSERT INTO visits (page, hits) VALUES
('/docs', 1),
('/docs', 1)
ON CONFLICT (page) DO UPDATE
SET hits = visits.hits + EXCLUDED.hits;Example explained
Line 1The first ('/docs', 1) row is inserted, then the second one conflicts with the row this same statement just created.
Line 2The server refuses to update a row it has already written in one command, because the outcome would depend on the order the source rows happened to be processed.
Line 3The error aborts the whole statement, so neither row lands and the counter is unchanged.
Line 4Fix it in the source data: feed the upsert from SELECT page, sum(hits) ... GROUP BY page so each key appears once.
Important notes
DO NOTHING absorbs only unique and exclusion constraint violations; a NOT NULL or CHECK failure still aborts the statement, and a sequence default is consumed before the conflict is detected, so skipped rows leave gaps in generated ids.
The syntax is dialect specific: SQLite matches this form, MySQL and MariaDB write INSERT ... ON DUPLICATE KEY UPDATE, and Oracle, Db2 and SQL Server use MERGE, but the fallback-on-a-unique-key model is the same everywhere.
Common mistakes
Naming a column that has no unique index, such as ON CONFLICT (email) on an unconstrained email: the statement is rejected with 'there is no unique or exclusion constraint matching the ON CONFLICT specification', so the upsert never runs at all.
Reading EXCLUDED as the stored row, then writing SET qty = EXCLUDED.qty when accumulation was intended: the running total is replaced by the single incoming value and the loss is silent, since no error is raised.
Sending a batch that repeats a key and expecting the increments to add up: the statement fails with 'cannot affect row a second time' and the entire batch is rolled back, so the load has to be deduplicated and retried.
Try it yourself
Change, predict, then run
Create word_counts(word text PRIMARY KEY, n integer NOT NULL) and insert ('the', 4), then write one upsert supplying ('the', 3) and ('and', 2) so that the ends at 7 and and is inserted with 2. Run the same upsert a second time and confirm the reaches 10 while the row count stays at 2.
Open the SQL workspaceCheck your understanding
A table has PRIMARY KEY (id) and a separate UNIQUE (email). You run INSERT ... ON CONFLICT (id) DO UPDATE ... with a row whose id is new but whose email already belongs to another row. What happens?
- The existing row with that email is updated instead
- The row is silently skipped, because ON CONFLICT was specified
- The statement fails with a unique violation on the email constraint
- The row is inserted and the email uniqueness is not checked
Show answer
The conflict target names one index, and only a violation of that index is converted into an update. Any other unique constraint is still enforced normally, so the insert fails. The first option is tempting but wrong: the server does not search for whichever constraint happened to conflict, and if it did it would have no basis for deciding that the email row is the one your SET clause meant.