INSERT, and the INSERT you can run twice

The nightly job that ran twice

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 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.

The shapes of INSERT

INSERT INTO payments (reference, student_id, amount, paid_on, method)
VALUES ('P-10441', 812, 4500.00, '2026-03-14', 'upi');

INSERT INTO payments (reference, student_id, amount, paid_on, method)
VALUES ('P-10442', 813, 4500.00, '2026-03-14', 'upi'),
       ('P-10443', 814, 3000.00, '2026-03-14', 'cash');

INSERT INTO payments (reference, student_id, amount, paid_on, method)
SELECT reference, student_id, amount, paid_on, method
FROM payments_clean
WHERE loaded_at > '2026-03-14';

Name 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.

Making it safe to repeat

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. ON CONFLICT says what to do instead:

INSERT INTO payments (reference, student_id, amount, paid_on, method)
SELECT ... FROM payments_clean
ON CONFLICT (reference) DO NOTHING;

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:

ON CONFLICT (reference) DO UPDATE
  SET amount = EXCLUDED.amount,
      method = EXCLUDED.method;

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.

What it needs underneath

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.

SQLite 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.

Two rows in the same statement with the same key

An 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.

Bulk loads

For 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.

The habit

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 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.