Postgres INSERT If Not Exists: Fix Duplicate Key Violations

TL;DR

If you're seeing ERROR: duplicate key value violates unique constraint "some_constraint", the cause is usually a race condition from a naive SELECT-then-INSERT pattern. Fix it by replacing that pattern with a single INSERT ... ON CONFLICT DO NOTHING statement that atomically inserts only when the row doesn't exist.

• Symptom: ERROR: duplicate key value violates unique constraint "users_email_key"
• Root cause: Two concurrent transactions both see no row, both attempt an INSERT, and the second one hits the unique constraint.
• Fix: Use INSERT INTO ... VALUES (...) ON CONFLICT (unique_column) DO NOTHING with a RETURNING clause to get the row's ID whether it was inserted or already existed.
• Verification: Run two concurrent psql sessions inserting the same value; only one row is created, and no error is thrown.

The error, decoded

You run an INSERT and PostgreSQL fires back:

ERROR:  duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=([e-mail]) already exists.

This happens when your application tries to insert a row that conflicts with an existing unique constraint—most often a primary key or a unique index on a column like email. The error is not a bug in PostgreSQL; it's the database enforcing data integrity. The real problem is that your application assumed the row didn't exist, but by the time the INSERT executed, another session had already inserted it.

This exact scenario has been asked over 700 times on Stack Overflow: Postgres: INSERT if does not exist already. The core question is how to perform an idempotent insert—one that succeeds whether the row is new or already present—without hitting a duplicate key violation.

Why SELECT-then-INSERT fails under concurrency

The most common (and broken) pattern looks like this:

-- Session 1
BEGIN;
SELECT id FROM users WHERE email = '[e-mail]';
-- No row returned → proceed to insert
INSERT INTO users (email, name) VALUES ('[e-mail]', 'Alice');
COMMIT;

If only one session runs this, it works. But under any concurrent load, two sessions can both execute the SELECT, both see no row, and both proceed to INSERT. The second INSERT hits the unique constraint and throws the error. This is a classic race condition—the check and the insert are not atomic.

Even wrapping the two statements in a SERIALIZABLE transaction—the highest isolation level—doesn't make them atomic. PostgreSQL's serializable isolation uses Serializable Snapshot Isolation (SSI) to detect conflicts, but it cannot turn two separate statements into one. You'd still get a serialization failure (SQLSTATE 40001) that you'd have to retry, and the retry loop would need to handle the duplicate key error anyway. The only correct solution is to push the existence check into the INSERT itself, which is exactly what ON CONFLICT does.

The WHERE NOT EXISTS anti-pattern

Another common attempt is using INSERT ... WHERE NOT EXISTS (SELECT 1 FROM ...). It looks atomic but isn't:

INSERT INTO users (email, name)
SELECT '[e-mail]', 'Alice'
WHERE NOT EXISTS (
  SELECT 1 FROM users WHERE email = '[e-mail]'
);

Under concurrency, two sessions can both evaluate the subquery as true and both insert, leading to the same duplicate key violation. The WHERE NOT EXISTS clause is evaluated once per statement, but the check and insert are not locked together across sessions. Only ON CONFLICT with a unique constraint provides atomicity.

The fix: INSERT ... ON CONFLICT DO NOTHING

PostgreSQL 9.5+ provides the ON CONFLICT clause, which turns an INSERT into an atomic "insert if not exists" operation. The syntax requires a unique constraint or exclusion constraint on the target column(s). Here's the minimal fix:

INSERT INTO users (email, name)
VALUES ('[e-mail]', 'Alice')
ON CONFLICT (email) DO NOTHING;

If a row with email = '[e-mail]' already exists, the statement does nothing and returns INSERT 0 0. No error is thrown. If the row doesn't exist, it inserts normally.

To also retrieve the id of the row—whether it was just inserted or already existed—add a RETURNING clause:

INSERT INTO users (email, name)
VALUES ('[e-mail]', 'Alice')
ON CONFLICT (email) DO NOTHING
RETURNING id;

When the row is inserted, RETURNING gives you the new id. When the row already exists, the statement returns zero rows. To always get the id, combine it with a fallback SELECT:

WITH ins AS (
  INSERT INTO users (email, name)
  VALUES ('[e-mail]', 'Alice')
  ON CONFLICT (email) DO NOTHING
  RETURNING id
)
SELECT id FROM ins
UNION ALL
SELECT id FROM users WHERE email = '[e-mail]';

This common table expression (CTE) ensures you always get exactly one id back, regardless of whether the insert happened. It's safe because the SELECT only runs if the INSERT returned nothing, and the unique constraint guarantees at most one matching row.

Two patterns that still trip you up

1. Using ON CONFLICT without a unique constraint

If you write ON CONFLICT (email) DO NOTHING but there is no unique constraint (or unique index) on email, PostgreSQL throws:

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

The conflict target must match an existing unique constraint. You can create one with:

CREATE UNIQUE INDEX users_email_key ON users (email);

Alternatively, you can use ON CONFLICT ON CONSTRAINT constraint_name if you know the constraint's name.

2. NULLs in unique constraints

A unique constraint treats NULL values as distinct—multiple rows with NULL in the constrained column are allowed. If your application logic expects NULL to mean "no value" and you want to prevent duplicate NULLs, you need a partial unique index:

CREATE UNIQUE INDEX users_email_unique_when_not_null
ON users (email) WHERE email IS NOT NULL;

Then use ON CONFLICT (email) WHERE email IS NOT NULL in your INSERT. Without the partial index, an ON CONFLICT on a column that allows NULL will never see a conflict for NULL values, and you'll end up with multiple rows where you expected only one.

Confirm it's safe

Open two psql sessions connected to the same database. In both, set up the table:

CREATE TABLE test_upsert (
  id SERIAL PRIMARY KEY,
  key TEXT UNIQUE,
  value TEXT
);

In session 1, run:

BEGIN;
INSERT INTO test_upsert (key, value)
VALUES ('x', 'first')
ON CONFLICT (key) DO NOTHING
RETURNING id;
-- Returns the new id, e.g., 1

Before committing, switch to session 2 and run the same INSERT:

BEGIN;
INSERT INTO test_upsert (key, value)
VALUES ('x', 'second')
ON CONFLICT (key) DO NOTHING
RETURNING id;
-- Blocks until session 1 commits or rolls back

Now commit session 1:

COMMIT;

Session 2 immediately unblocks and returns INSERT 0 0—no row returned, no error. The row was inserted only once. If you then query:

SELECT * FROM test_upsert;

You'll see exactly one row with key = 'x' and value = 'first'. The second insert was silently skipped.

This demonstrates that ON CONFLICT is atomic and safe under concurrent writes. For deeper concurrency issues like serialization failures, see Fix Postgres 'Could Not Serialize Access' (40001).

Using Drizzle ORM with PostgreSQL enums and upserts

If you're using Drizzle ORM, you can define PostgreSQL enums with pgEnum from drizzle-orm/pg-core and use Drizzle's onConflictDoNothing for idempotent inserts.

First, define an enum type. This maps to PostgreSQL's CREATE TYPE ... AS ENUM:

import { pgEnum } from 'drizzle-orm/pg-core';

export const userRoleEnum = pgEnum('user_role', ['admin', 'member', 'viewer']);

When you run drizzle-kit generate, it produces a migration that executes:

CREATE TYPE "user_role" AS ENUM ('admin', 'member', 'viewer');

Then define a table that uses the enum:

import { pgTable, serial, text } from 'drizzle-orm/pg-core';
import { userRoleEnum } from './enums';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').unique().notNull(),
  role: userRoleEnum('role').default('member'),
});

To insert a user only if the email doesn't exist, use .onConflictDoNothing():

import { db } from './db';
import { users } from './schema';

await db.insert(users)
  .values({ email: '[e-mail]', role: 'admin' })
  .onConflictDoNothing({ target: users.email })
  .returning({ id: users.id });

This generates the same INSERT ... ON CONFLICT DO NOTHING SQL, with the conflict target on the unique email column. The .returning() clause gives you the inserted row's id if the insert succeeded; if a conflict occurred, it returns an empty array. To always get the id, you can fall back to a SELECT in your application code, or use a raw SQL CTE as shown earlier.

Drizzle's onConflictDoNothing works with any unique constraint, including composite keys and partial indexes, by specifying the appropriate target.

FAQ

How do I avoid duplicate key violations when multiple processes insert at the same time?

Use INSERT ... ON CONFLICT DO NOTHING with a unique constraint on the column(s) that define uniqueness. This single atomic statement checks for conflicts and skips the insert if a row already exists, eliminating the race condition inherent in a separate SELECT-then-INSERT pattern.

Can I use INSERT ... ON CONFLICT to update some columns but not others?

Yes, with ON CONFLICT DO UPDATE SET column = EXCLUDED.column. You can specify exactly which columns to update and even add a WHERE clause to conditionally apply the update only when certain criteria are met. For example:

INSERT INTO users (email, name, login_count)
VALUES ('[e-mail]', 'Alice', 1)
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name,
    login_count = users.login_count + 1
WHERE users.login_count < 100;

What if I need to insert multiple rows and skip duplicates?

You can insert multiple rows in a single INSERT and use ON CONFLICT to skip any that conflict:

INSERT INTO users (email, name)
VALUES
  ('[e-mail]', 'Alice'),
  ('[e-mail]', 'Bob'),
  ('[e-mail]', 'Alice Dup')
ON CONFLICT (email) DO NOTHING;

Rows that conflict are silently ignored; non-conflicting rows are inserted. The statement returns the count of rows actually inserted.

Is ON CONFLICT atomic?

Yes. The entire INSERT ... ON CONFLICT is a single statement that runs atomically. There is no window between checking for a conflict and performing the insert, so no race condition can occur.

What about PostgreSQL versions before 9.5?

If you're stuck on an older version, you can use a PL/pgSQL function that catches unique_violation exceptions:

CREATE OR REPLACE FUNCTION safe_insert(p_email TEXT, p_name TEXT)
RETURNS INT AS $$
DECLARE
  v_id INT;
BEGIN
  INSERT INTO users (email, name) VALUES (p_email, p_name)
  RETURNING id INTO v_id;
  RETURN v_id;
EXCEPTION WHEN unique_violation THEN
  SELECT id INTO v_id FROM users WHERE email = p_email;
  RETURN v_id;
END;
$$ LANGUAGE plpgsql;

This approach is slower and more complex than ON CONFLICT, so upgrading to 9.5+ is strongly recommended.

Related

• Fix Postgres 'Could Not Serialize Access' (40001)
• Fix Foreign Key Constraint Violation in Supabase (23503)
• Fix: psql: command not found (Install PostgreSQL Client)
• Select the First Row per Group in Supabase Postgres

---

Originally published at https://www.iloveblogs.blog