{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"insert-and-upsert","title":"INSERT, and the INSERT you can run twice","url":"https://addaly.com/learn/data-and-sql/insert-and-upsert","position":57,"of":66,"minutes":8,"answer":"An INSERT that must be safe to re-run needs a unique key and an ON CONFLICT clause, because the database detects the duplicate by inserting into the unique index.","keyIdea":"An INSERT that must be safe to re-run needs a unique key and an ON CONFLICT clause, because the database detects the duplicate by inserting into the unique index, and without that index it cannot detect one at all."},"body":{"format":"markdown","text":"## The nightly job that ran twice\n\nA script inserts the day's payments from the provider's file. It crashes halfway, somebody re-runs it, and now 3,000 payments exist twice. Nothing in `INSERT` prevents that; it does what it says. The property you want is that running the job again produces the same table as running it once, which has a name, idempotence, and a specific way to get it.\n\n## The shapes of INSERT\n\n```sql\nINSERT INTO payments (reference, student_id, amount, paid_on, method)\nVALUES ('P-10441', 812, 4500.00, '2026-03-14', 'upi');\n\nINSERT INTO payments (reference, student_id, amount, paid_on, method)\nVALUES ('P-10442', 813, 4500.00, '2026-03-14', 'upi'),\n       ('P-10443', 814, 3000.00, '2026-03-14', 'cash');\n\nINSERT INTO payments (reference, student_id, amount, paid_on, method)\nSELECT reference, student_id, amount, paid_on, method\nFROM payments_clean\nWHERE loaded_at > '2026-03-14';\n```\n\nName the columns every time. `INSERT INTO payments VALUES (...)` with no column list depends on the order columns were created in, and breaks the day someone adds one. The multi-row form is far faster than one statement per row, because each statement is a round trip to the server; the `INSERT ... SELECT` form moves rows from a staging table into a real one and is how the previous module's clean view becomes a table. `RETURNING id` at the end of any of them gives back the generated keys, which is how an application learns the id of the row it just created without a second query.\n\n## Making it safe to repeat\n\nThe reference is unique, so the second run fails on the first duplicate with a constraint error, and loads nothing. That is safe but unhelpful. `ON CONFLICT` says what to do instead:\n\n```sql\nINSERT INTO payments (reference, student_id, amount, paid_on, method)\nSELECT ... FROM payments_clean\nON CONFLICT (reference) DO NOTHING;\n```\n\nRows whose reference already exists are skipped; the rest go in. Run it ten times and the table is the same as after once. Or, when the new row should replace the old:\n\n```sql\nON CONFLICT (reference) DO UPDATE\n  SET amount = EXCLUDED.amount,\n      method = EXCLUDED.method;\n```\n\n`EXCLUDED` is the row that would have been inserted. This is the upsert, insert-or-update in one statement, and it is how a daily feed of customer records is kept current without first checking whether each one exists.\n\n## What it needs underneath\n\n`ON CONFLICT (reference)` works only because there is a unique index on `reference`. The database detects the conflict by trying to insert into that index. Without one you get `there is no unique or exclusion constraint matching the ON CONFLICT specification`, and the fix is the constraint, not a different statement. This is also why an upsert on a column that is *usually* unique but not declared so is not possible: the database will not guess.\n\nSQLite uses the same `ON CONFLICT` syntax. MySQL says `INSERT ... ON DUPLICATE KEY UPDATE amount = VALUES(amount)`. SQL Server and Oracle, and Postgres from version 15, have `MERGE`, which can also delete and is more general and more verbose. All of them rest on a unique key.\n\n## Two rows in the same statement with the same key\n\nAn `INSERT ... SELECT ... ON CONFLICT` whose source contains the same reference twice fails with `ON CONFLICT DO UPDATE command cannot affect row a second time`. The source has to be deduplicated first, with the `row_number` pattern from the previous module. The error is doing you a favour: it has found a duplicate in the file before it found its way in.\n\n## Bulk loads\n\nFor a million rows, `COPY` is ten to a hundred times faster than any form of `INSERT`, because it streams the data in one operation instead of parsing a statement per batch. The pattern for a big idempotent load is `COPY` into a staging table with no constraints, then one `INSERT ... SELECT ... ON CONFLICT` from staging into the real table. The constraints are checked once, at the boundary between the two.\n\n## The habit\n\nAsk of every job that writes: what happens if this runs twice? If the answer is \"duplicates\", the job needs a unique key and an `ON CONFLICT`. If the answer is \"an error\", the job is safe but will page somebody. If the answer is \"the same table either way\", the job is finished.\n\n> Figure: A load that produces the same table whether it runs once or ten times. Steps: 1. COPY into staging (no constraints, so nothing can fail); 2. Deduplicate the source (row_number over the key, keep number one); 3. INSERT ... ON CONFLICT (DO NOTHING, or DO UPDATE from EXCLUDED); 4. Run it again (and the table does not change). ON CONFLICT works only because a unique index exists to detect the conflict in; without one the database cannot guess which column made the row a duplicate. Deduplicating the source first is not optional either: the same key twice in one statement is an error, and a useful one."},"check":{"question":"A nightly load uses `INSERT ... SELECT ... ON CONFLICT (email) DO UPDATE` into a customers table and fails with \"there is no unique or exclusion constraint matching the ON CONFLICT specification\". Emails are in fact unique in the data. What is the fix?","options":[{"text":"Change `DO UPDATE` to `DO NOTHING`, since DO UPDATE requires a primary key and DO NOTHING works on any column.","correct":false,"tempting":"Both forms need a unique index on the conflict column; the action taken on conflict is irrelevant to detecting it."},{"text":"Deduplicate the source with `DISTINCT` first, because the error means the incoming rows contain the same email twice.","correct":false,"tempting":"That situation produces a different error, about affecting a row a second time; this message is about the target table's constraints."},{"text":"Add `UNIQUE (email)` to the table, because the conflict is detected by the unique index and the data being unique in practice is not enough.","correct":true},{"text":"Replace `ON CONFLICT` with a `WHERE NOT EXISTS` subquery, since Postgres can only detect conflicts on the primary key column and email is not the key.","correct":false,"tempting":"ON CONFLICT works on any unique index, and the subquery version is not safe under concurrent inserts; the constraint is the fix."}]},"prev":{"slug":"create-table-and-constraints","title":"CREATE TABLE: a constraint is a test that runs on every write","url":"https://addaly.com/learn/data-and-sql/create-table-and-constraints"},"next":{"slug":"update-and-delete-without-regret","title":"UPDATE and DELETE: rehearse it as a SELECT","url":"https://addaly.com/learn/data-and-sql/update-and-delete-without-regret"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'INSERT, and the INSERT you can run twice', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/insert-and-upsert (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"INSERT, and the INSERT you can run twice","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":6,"slug":"changing-data-safely","title":"Changing data safely"},"answer":null,"keyIdea":"An INSERT that must be safe to re-run needs a unique key and an ON CONFLICT clause, because the database detects the duplicate by inserting into the unique index, and without that index it cannot detect one at all.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"type":"flow","steps":[{"label":"COPY into staging","detail":"no constraints, so nothing can fail"},{"label":"Deduplicate the source","detail":"row_number over the key, keep number one"},{"label":"INSERT ... ON CONFLICT","detail":"DO NOTHING, or DO UPDATE from EXCLUDED"},{"label":"Run it again","detail":"and the table does not change"}],"title":"A load that produces the same table whether it runs once or ten times","caption":"ON CONFLICT works only because a unique index exists to detect the conflict in; without one the database cannot guess which column made the row a duplicate. Deduplicating the source first is not optional either: the same key twice in one statement is an error, and a useful one."},"alt":"Figure: A load that produces the same table whether it runs once or ten times. Steps: 1. COPY into staging (no constraints, so nothing can fail); 2. Deduplicate the source (row_number over the key, keep number one); 3. INSERT ... ON CONFLICT (DO NOTHING, or DO UPDATE from EXCLUDED); 4. Run it again (and the table does not change). ON CONFLICT works only because a unique index exists to detect the conflict in; without one the database cannot guess which column made the row a duplicate. Deduplicating the source first is not optional either: the same key twice in one statement is an error, and a useful one."}},"blocks":[{"t":"h2","id":"the-nightly-job-that-ran-twice","runs":[{"text":"The nightly job that ran twice"}]},{"t":"p","runs":[{"text":"A script inserts the day’s payments from the provider’s file. It crashes halfway, somebody re-runs it, and now 3,000 payments exist twice. Nothing in "},{"text":"INSERT","code":true},{"text":" prevents that; it does what it says. The property you want is that running the job again produces the same table as running it once, which has a name, idempotence, and a specific way to get it."}]},{"t":"h2","id":"the-shapes-of-insert","runs":[{"text":"The shapes of INSERT"}]},{"t":"code","lang":"sql","label":"SQL","text":"INSERT INTO payments (reference, student_id, amount, paid_on, method)\nVALUES ('P-10441', 812, 4500.00, '2026-03-14', 'upi');\n\nINSERT INTO payments (reference, student_id, amount, paid_on, method)\nVALUES ('P-10442', 813, 4500.00, '2026-03-14', 'upi'),\n       ('P-10443', 814, 3000.00, '2026-03-14', 'cash');\n\nINSERT INTO payments (reference, student_id, amount, paid_on, method)\nSELECT reference, student_id, amount, paid_on, method\nFROM payments_clean\nWHERE loaded_at > '2026-03-14';"},{"t":"p","runs":[{"text":"Name the columns every time. "},{"text":"INSERT INTO payments VALUES (...)","code":true},{"text":" with no column list depends on the order columns were created in, and breaks the day someone adds one. The multi-row form is far faster than one statement per row, because each statement is a round trip to the server; the "},{"text":"INSERT ... SELECT","code":true},{"text":" form moves rows from a staging table into a real one and is how the previous module’s clean view becomes a table. "},{"text":"RETURNING id","code":true},{"text":" at the end of any of them gives back the generated keys, which is how an application learns the id of the row it just created without a second query."}]},{"t":"h2","id":"making-it-safe-to-repeat","runs":[{"text":"Making it safe to repeat"}]},{"t":"p","runs":[{"text":"The reference is unique, so the second run fails on the first duplicate with a constraint error, and loads nothing. That is safe but unhelpful. "},{"text":"ON CONFLICT","code":true},{"text":" says what to do instead:"}]},{"t":"code","lang":"sql","label":"SQL","text":"INSERT INTO payments (reference, student_id, amount, paid_on, method)\nSELECT ... FROM payments_clean\nON CONFLICT (reference) DO NOTHING;"},{"t":"p","runs":[{"text":"Rows whose reference already exists are skipped; the rest go in. Run it ten times and the table is the same as after once. Or, when the new row should replace the old:"}]},{"t":"code","lang":"sql","label":"SQL","text":"ON CONFLICT (reference) DO UPDATE\n  SET amount = EXCLUDED.amount,\n      method = EXCLUDED.method;"},{"t":"p","runs":[{"text":"EXCLUDED","code":true},{"text":" is the row that would have been inserted. This is the upsert, insert-or-update in one statement, and it is how a daily feed of customer records is kept current without first checking whether each one exists."}]},{"t":"h2","id":"what-it-needs-underneath","runs":[{"text":"What it needs underneath"}]},{"t":"p","runs":[{"text":"ON CONFLICT (reference)","code":true},{"text":" works only because there is a unique index on "},{"text":"reference","code":true},{"text":". The database detects the conflict by trying to insert into that index. Without one you get "},{"text":"there is no unique or exclusion constraint matching the ON CONFLICT specification","code":true},{"text":", and the fix is the constraint, not a different statement. This is also why an upsert on a column that is "},{"text":"usually","i":true},{"text":" unique but not declared so is not possible: the database will not guess."}]},{"t":"p","runs":[{"text":"SQLite uses the same "},{"text":"ON CONFLICT","code":true},{"text":" syntax. MySQL says "},{"text":"INSERT ... ON DUPLICATE KEY UPDATE amount = VALUES(amount)","code":true},{"text":". SQL Server and Oracle, and Postgres from version 15, have "},{"text":"MERGE","code":true},{"text":", which can also delete and is more general and more verbose. All of them rest on a unique key."}]},{"t":"h2","id":"two-rows-in-the-same-statement-with-the-same-key","runs":[{"text":"Two rows in the same statement with the same key"}]},{"t":"p","runs":[{"text":"An "},{"text":"INSERT ... SELECT ... ON CONFLICT","code":true},{"text":" whose source contains the same reference twice fails with "},{"text":"ON CONFLICT DO UPDATE command cannot affect row a second time","code":true},{"text":". The source has to be deduplicated first, with the "},{"text":"row_number","code":true},{"text":" pattern from the previous module. The error is doing you a favour: it has found a duplicate in the file before it found its way in."}]},{"t":"h2","id":"bulk-loads","runs":[{"text":"Bulk loads"}]},{"t":"p","runs":[{"text":"For a million rows, "},{"text":"COPY","code":true},{"text":" is ten to a hundred times faster than any form of "},{"text":"INSERT","code":true},{"text":", because it streams the data in one operation instead of parsing a statement per batch. The pattern for a big idempotent load is "},{"text":"COPY","code":true},{"text":" into a staging table with no constraints, then one "},{"text":"INSERT ... SELECT ... ON CONFLICT","code":true},{"text":" from staging into the real table. The constraints are checked once, at the boundary between the two."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"Ask of every job that writes: what happens if this runs twice? If the answer is “duplicates”, the job needs a unique key and an "},{"text":"ON CONFLICT","code":true},{"text":". If the answer is “an error”, the job is safe but will page somebody. If the answer is “the same table either way”, the job is finished."}]}],"check":{"question":[{"text":"A nightly load uses "},{"text":"INSERT ... SELECT ... ON CONFLICT (email) DO UPDATE","code":true},{"text":" into a customers table and fails with “there is no unique or exclusion constraint matching the ON CONFLICT specification”. Emails are in fact unique in the data. What is the fix?"}],"options":[{"text":[{"text":"Change "},{"text":"DO UPDATE","code":true},{"text":" to "},{"text":"DO NOTHING","code":true},{"text":", since DO UPDATE requires a primary key and DO NOTHING works on any column."}],"correct":false,"tempting":[{"text":"Both forms need a unique index on the conflict column; the action taken on conflict is irrelevant to detecting it."}]},{"text":[{"text":"Deduplicate the source with "},{"text":"DISTINCT","code":true},{"text":" first, because the error means the incoming rows contain the same email twice."}],"correct":false,"tempting":[{"text":"That situation produces a different error, about affecting a row a second time; this message is about the target table’s constraints."}]},{"text":[{"text":"Add "},{"text":"UNIQUE (email)","code":true},{"text":" to the table, because the conflict is detected by the unique index and the data being unique in practice is not enough."}],"correct":true,"tempting":null},{"text":[{"text":"Replace "},{"text":"ON CONFLICT","code":true},{"text":" with a "},{"text":"WHERE NOT EXISTS","code":true},{"text":" subquery, since Postgres can only detect conflicts on the primary key column and email is not the key."}],"correct":false,"tempting":[{"text":"ON CONFLICT works on any unique index, and the subquery version is not safe under concurrent inserts; the constraint is the fix."}]}],"order":[0,1,2,3],"why":null},"prev":{"slug":"create-table-and-constraints","title":"CREATE TABLE: a constraint is a test that runs on every write","minutes":9},"next":{"slug":"update-and-delete-without-regret","title":"UPDATE and DELETE: rehearse it as a SELECT","minutes":8}}}