INSERT ... ON CONFLICT DO NOTHING in PostgreSQL: Idempotent Inserts Without Errors

ON CONFLICT DO NOTHING turns INSERT into a safe, repeatable operation: if a row violates a unique constraint, PostgreSQL does not error out, it silently skips it. This is the workhorse of idempotent migrations, seeders, and background jobs that might run twice.

The idempotent insert

A plain INSERT of a duplicate key throws duplicate key value violates unique constraint and rolls back the whole command. Add ON CONFLICT DO NOTHING and the conflicting row simply drops out of the set to insert, while the rest go through.

INSERT INTO users (id, email, name, country)
VALUES (1, 'ada@example.com', 'Ada', 'GB')
ON CONFLICT (id) DO NOTHING;

Run this query ten times in a row and the table state after the first run never changes. That is idempotency: the result depends on the data, not on how many times you fired the statement.

• The command does not fail: the transaction stays alive, no ROLLBACK.
• Only the conflicting row is skipped, not the whole batch.
• The existing row is left untouched -- its values stay as they were.

Conflict target versus constraint name

PostgreSQL needs to know which conflict you are willing to ignore. There are two ways to spell it out.

The first is a conflict target: a list of columns (or an expression) backed by a unique index.

INSERT INTO users (email, name, country)
VALUES ('ada@example.com', 'Ada', 'GB')
ON CONFLICT (email) DO NOTHING;

The second is an explicit constraint name via ON CONSTRAINT:

INSERT INTO users (email, name, country)
VALUES ('ada@example.com', 'Ada', 'GB')
ON CONFLICT ON CONSTRAINT users_email_key DO NOTHING;

And there is a third, broadest form -- ON CONFLICT DO NOTHING with no target at all: then PostgreSQL suppresses a conflict on any unique constraint or primary key.

Gotcha: targetless DO NOTHING is dangerous. If the table has both PRIMARY KEY (id) and UNIQUE (email), the targetless form swallows a conflict on either. A row with a fresh id but an already-taken email vanishes silently, and you assume the insert succeeded. Name a specific target when the reason for skipping matters.

RETURNING stays silent on skipped rows

RETURNING hands back only the rows that were actually inserted. Rows skipped by a conflict never appear in the output -- a frequent source of confusion.

INSERT INTO orders (id, user_id, amount, status)
VALUES (5001, 1, 99.90, 'paid')
ON CONFLICT (id) DO NOTHING
RETURNING id, status;

If id = 5001 already exists, the query returns zero rows. At the application level this is handy: an empty RETURNING means "the row was already there." If you want the row regardless of whether it was inserted, you need a separate SELECT or a DO UPDATE (see the upsert article).

• Inserted -- the row is in RETURNING.
• Skipped on conflict -- no row.
• The driver's GET DIAGNOSTICS / rowcount also reports 0 for a skip.

Bulk data seeding

The most common scenario is loading a reference set of rows without knowing which already exist. DO NOTHING removes the need for WHERE NOT EXISTS and the race conditions that come with it.

INSERT INTO employees (id, name, manager_id, dept, salary)
VALUES
  (1, 'Grace', NULL, 'eng', 180000),
  (2, 'Linus', 1,    'eng', 150000),
  (3, 'Margaret', 1, 'eng', 150000)
ON CONFLICT (id) DO NOTHING;

Useful details for batch inserts:

• Within a single command, duplicates inside the input set are also filtered: the first row wins, later rows with the same key are skipped.
• It is safe under concurrency: a competing insert of the same key will not crash your INSERT.
• It pairs well with INSERT ... SELECT to move data from staging into the main table without duplicates.

Other engines spell it differently. In MySQL the nearest analog is INSERT IGNORE INTO ..., but it also suppresses some unrelated errors (truncation, bad values), so it is less precise. In ClickHouse uniqueness is usually not enforced on insert at all: deduplication is done by the ReplacingMergeTree engine during merges, asynchronously, rather than at INSERT time.

When you need to update the existing row instead of skipping it, that is ON CONFLICT ... DO UPDATE (upsert): a separate tool with its own semantics and the EXCLUDED pseudo-table, covered in its own article.