Concept · SQL & Querying

Run the DML twice — a correct upsert lands the same state both times (bridge to run-level idempotency).

Jobs re-run — retries, backfills, a human clicking twice — so 'correct if executed exactly once' is not correct. Idempotency has two ingredients: a key that means identity (the natural key of the fact, not an auto-increment), and a declared collision behavior (update or do-nothing). The run-twice test is the proof: execute, snapshot, execute again, diff — empty diff or it isn't idempotent.

See it break

daily

windmill	visitors

The trap

INSERT INTO
  daily
VALUES
  ('plum',
  10
);

INSERT INTO
  daily
VALUES
  ('plum',
  10
);

SELECT
  windmill,
  COUNT(*) AS rows
FROM
  daily
GROUP BY
  windmill;

The fix

INSERT INTO
  daily
SELECT
  'plum',
  10
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      daily
    WHERE
      windmill = 'plum'
  );

INSERT INTO
  daily
SELECT
  'plum',
  10
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      daily
    WHERE
      windmill = 'plum'
  );

SELECT
  windmill,
  COUNT(*) AS rows
FROM
  daily
GROUP BY
  windmill;

INPUT	Your query	The fix
plum	2	1

Run twice, the plain INSERT left two duplicate 'plum' rows — a write is idempotent only if a rerun changes nothing, which needs a key and a declared conflict action (ON CONFLICT / MERGE).

The rule

ON CONFLICT (key) DO UPDATE/NOTHING, or MERGE with both branches; the run-twice test in CI.

Shows up elsewhere

MERGE with a duplicated source key INSERT ... SELECT column position

Try one →Prove it in practice →