Postgres error 23505: unique violation
duplicate key value violates unique constraint "x_pkey"
The row would duplicate a value that a primary key or unique index requires to be unique.
Common causes
- Inserting a row whose key already exists, often a retried request or a double-clicked submit.
- An identity or serial sequence behind the real maximum id after data was copied in with explicit ids, so new inserts reuse old ids.
- Two concurrent transactions inserting the same natural key.
How to fix it
- Use an upsert when a duplicate is expected: insert ... on conflict (key) do update or do nothing.
- After loading data with explicit ids, move the sequence past the maximum.
- Handle the error as a normal outcome in the app (for example "this email is already registered").
-- sequence behind after an import
select setval(pg_get_serial_sequence('orders', 'id'), (select max(id) from orders));
-- idempotent insert
insert into subscriptions (user_id, plan) values ($1, $2)
on conflict (user_id) do update set plan = excluded.plan;Through a REST API
PostgREST, the REST layer behind supabase-js and Scribase, answers HTTP 409 for this error and returns the SQLSTATE in the code field of the JSON error body. PostgREST answers 409 Conflict. With supabase-js, .upsert() sends ON CONFLICT for you; pass onConflict when the unique key is not the primary key.
Questions
What does Postgres error 23505 mean?
23505 is unique_violation in class 23 (Integrity Constraint Violation). The row would duplicate a value that a primary key or unique index requires to be unique.
How do I fix 23505?
Use an upsert when a duplicate is expected: insert ... on conflict (key) do update or do nothing. After loading data with explicit ids, move the sequence past the maximum. Handle the error as a normal outcome in the app (for example "this email is already registered").
What HTTP status does a REST API return for 23505?
PostgREST, the REST layer behind supabase-js, answers 409.