PostgreSQL Error 23505

SQLSTATE

23505

Condition name

unique_violation

Product

PostgreSQL

Versions

PostgreSQL 13 to 18 (examples run on PostgreSQL 18.6 in PGlite)

Category

Integrity Constraints

Last reviewed

24 October 2026

What it means

SQLSTATE 23505 means a row would store a value that already exists in a PRIMARY KEY, UNIQUE constraint or unique index, so PostgreSQL rejected the statement.

A PRIMARY KEY or UNIQUE constraint is enforced by a unique index, and each key value may appear only once. When an INSERT, UPDATE or COPY would create a second row with an existing value, PostgreSQL stops the statement and raises 23505 (unique_violation). The message names the constraint or index, and the DETAIL line shows the exact key and value: Key (email)=(sara@example.com) already exists. For a key over several columns, DETAIL lists every column and value.

Inside a transaction, the error also aborts the transaction: every later statement fails with 25P02 until you ROLLBACK (or roll back to a savepoint). Building a unique index or adding a UNIQUE constraint over data that already has duplicates raises the same SQLSTATE with a different first line, "could not create unique index".

Common causes

• The same row is inserted twice: a retried request, a double submit or an import that runs again.
• The sequence behind a serial or identity column is behind the data: rows were inserted with explicit ids (an import, a restore of data only, a manual fix), so the next generated id is one that already exists.
• An UPDATE changes a unique column, such as an email address, to a value another row already has.
• A unique index or constraint is added to a column that already holds duplicate values.
• An INSERT ... SELECT or COPY brings in a source with duplicates, among its own rows or against existing ones.

Diagnose it

Read-only These queries only read the system catalogs (pg_catalog, information_schema) and statistics views such as pg_stat_activity and pg_locks. They do not change data, roles or settings, so they are safe to run first.

SELECT conname, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'customers'::regclass
  AND contype IN ('p', 'u')
ORDER BY conname;

SELECT *
FROM customers
WHERE email = 'sara@example.com';  -- the value from the DETAIL line

SELECT pg_get_serial_sequence('customers', 'id') AS sequence_name;

SELECT last_value, is_called FROM customers_id_seq;
SELECT max(id) AS highest_id FROM customers;

SELECT name, count(*) AS copies
FROM people
GROUP BY name
HAVING count(*) > 1
ORDER BY copies DESC;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

INSERT INTO customers (email, name)
VALUES ('sara@example.com', 'Sara Ahmed')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

INSERT INTO customers (email, name)
SELECT email, name FROM staging_customers
ON CONFLICT (email) DO NOTHING;

SELECT setval(pg_get_serial_sequence('customers', 'id'),
              (SELECT max(id) FROM customers));

CREATE UNIQUE INDEX CONCURRENTLY ux_people_name ON people (name);

Examples

Inserting an email address that is already registered

CREATE TABLE customers (
    id    serial PRIMARY KEY,
    email text UNIQUE,
    name  text
);

INSERT INTO customers (email, name) VALUES ('sara@example.com', 'Sara');
INSERT INTO customers (email, name) VALUES ('sara@example.com', 'Sara B');

ERROR:  duplicate key value violates unique constraint "customers_email_key"
DETAIL:  Key (email)=(sara@example.com) already exists.

A sequence that is behind the data

The failed INSERT still used up sequence value 1, so retrying it gets id 2 and succeeds here. With many imported rows the collisions continue until the sequence passes the highest id, which is why the fix is setval.

CREATE TABLE customers (
    id    serial PRIMARY KEY,
    email text UNIQUE,
    name  text
);

-- rows loaded with explicit ids, for example from an import
INSERT INTO customers (id, email, name) VALUES (1, 'sara@example.com', 'Sara');

-- the sequence still starts at 1
INSERT INTO customers (email, name) VALUES ('ali@example.com', 'Ali');

ERROR:  duplicate key value violates unique constraint "customers_pkey"
DETAIL:  Key (id)=(1) already exists.

A duplicate in a multi-column key

CREATE TABLE order_lines (
    order_id int,
    line_no  int,
    PRIMARY KEY (order_id, line_no)
);

INSERT INTO order_lines (order_id, line_no) VALUES (10, 1), (10, 1);

ERROR:  duplicate key value violates unique constraint "order_lines_pkey"
DETAIL:  Key (order_id, line_no)=(10, 1) already exists.

Adding a unique index over existing duplicates

CREATE TABLE people (id int, name text);
INSERT INTO people VALUES (1, 'Sara'), (2, 'Sara');

CREATE UNIQUE INDEX ux_people_name ON people (name);

ERROR:  could not create unique index "ux_people_name"
DETAIL:  Key (name)=(Sara) is duplicated.

How to prevent it

Let PostgreSQL generate surrogate keys with an identity column (GENERATED ALWAYS AS IDENTITY) or serial, and do not insert explicit ids into them; after any import that does, run setval as shown above. Make writes that may run twice idempotent with ON CONFLICT.

Keep the unique constraint even when the application checks first, and handle SQLSTATE 23505 in your code (most drivers expose the SQLSTATE and the constraint name) so a duplicate becomes a clear message such as "this email is already registered". Remember that the error aborts the current transaction.

Common questions

Learn more on CodeWithSQL

• SQL constraints explained
• Primary key vs foreign key

Official documentation

• PostgreSQL error codes (Appendix A)
• INSERT ... ON CONFLICT
• Constraints: unique constraints and primary keys
• Sequence manipulation functions (setval)

Last technically reviewed 24 October 2026; examples and diagnostics run on PostgreSQL 18.6 (PGlite 0.5.11). SQL-level examples were run in PGlite (PostgreSQL compiled to WebAssembly); examples that need a running server are shown as documented in the PostgreSQL documentation.