App Finishers

Fix guide · updated 2026-11-16

Supabase "duplicate key value violates unique constraint" (23505)

{"code":"23505","details":"Key (id)=(1) already exists.","hint":null,"message":"duplicate key value violates unique constraint \"items_pkey\""}

Saving a row fails with duplicate key value violates unique constraint "profiles_pkey", code 23505, HTTP 409 Conflict. A row with that key already exists, and that column must be unique. The constraint's name and the details line say which column and which value clashed. We ran Supabase's Data API (PostgREST 13) with the requests supabase-js sends (version 2.119, November 2026) for each case below.

What the error means

Every primary key and every unique column has a constraint, usually named table_pkey or table_column_key. Postgres refuses a second row with the same value there, and the answer tells you exactly what clashed:

"message": "duplicate key value violates unique constraint \"profiles_username_key\"",
"details": "Key (username)=(ana) already exists."

Then the question is what your app wanted: replace the existing row, skip it, or save a different value.

1. The row already exists: update or upsert

The clash is on _pkey, often with the user's id: your app inserts a row that was already created. One setup does it twice: a database trigger creates the profile at sign-up, and the app inserts the profile again right after. A button pressed twice does the same when the id comes from your app. In our test, inserting the same id twice gave this 409.

If the row may already be there, update it, or use upsert(), which inserts or replaces. In our test, an upsert on the primary key replaced the row:

const { error } = await supabase.from('profiles').upsert({ id: user.id, name })

On a table with Row Level Security, an upsert also needs an UPDATE policy. In our test, with only select and insert policies, upserting an existing row failed with "new row violates row-level security policy (USING expression)".

2. upsert() without onConflict

upsert() checks the primary key unless you name another column. Take a settings table with its own id and a unique user_id: upsert({ user_id, theme }) has no id, so Postgres tries a new row, and user_id clashes. In our test that gave 23505 on settings_user_id_key. With onConflict, the same call updated the row:

const { error } = await supabase
  .from('settings')
  .upsert({ user_id: user.id, theme }, { onConflict: 'user_id' })

onConflict must name a column, or several separated by commas, that has a unique constraint. On a column without one, our test got 42P10 "there is no unique or exclusion constraint matching the ON CONFLICT specification". To add one (it fails if duplicates are already in the table):

alter table public.settings add constraint settings_user_id_key unique (user_id);

3. A value someone else already has

The clash is on a column the user chooses, like profiles_username_key with Key (username)=(ana). Nothing is broken: the name is taken. Catch that code and say so:

const { error } = await supabase.from('profiles').update({ username }).eq('id', user.id)
if (error?.code === '23505') {
  // show: "This username is already taken"
}

4. The id counter is behind the rows

The clash is on _pkey with a small number, like Key (id)=(1) already exists, on a table whose ids the database numbers by itself. Rows added with their own ids, from a CSV import, a migration or a seed file, don't move that counter. In our test, after importing ids 1 to 3, the next three inserts failed with ids 1, 2 and 3, and the fourth worked: each failed try still moves the counter, which is why the error can seem to come and go. Move the counter past the highest id, in the SQL Editor:

select setval(pg_get_serial_sequence('public.items', 'id'), (select max(id) from public.items));

Skip duplicates instead of failing

For imports where existing rows should stay as they are, pass ignoreDuplicates: true. In our test the request succeeded and the existing row didn't change:

const { error } = await supabase.from('profiles').upsert(rows, { ignoreDuplicates: true })

If an AI tool built your app

Give your AI tool this prompt, with your table and constraint name:

Saving to the profiles table fails with duplicate key value violates unique constraint "profiles_pkey". If the row can already exist (for example a profile created by a sign-up trigger), use upsert, with onConflict set to the unique column when it isn't the primary key, or update the row. For values users choose, like usernames, catch error.code === '23505' and show "already taken". Disable the save button while a request runs. Don't change anything else.

If the clash is on an id after an import (case 4), run the setval query yourself.

Not quite your error?

Still stuck?

Take the details line, Key (column)=(value), and look up the row that already holds that value: select * from public.profiles where username = 'ana'; in the SQL Editor. The existing row, and its created_at if the table has one, usually shows which of the cases above you're in.

Other Supabase problems? See Supabase fixes.