Supabase "duplicate key value violates unique constraint", explained

Someone fills in your waitlist form, waits, and presses the button again. The first press had gone through. The second one comes back as an error, and the browser console has all of it:

{
  "code": "23505",
  "details": "Key (email)=(maria@example.com) already exists.",
  "hint": null,
  "message": "duplicate key value violates unique constraint \"waitlist_email_key\""
}

Supabase "duplicate key value violates unique constraint" means Postgres refused to add the row because another row already holds the same value in a column that allows each value once. Here the column is email, and Maria is already on your list.

The advice passed around for this error is to switch the insert to an upsert. Sometimes that is right. On a table your visitors may only add to, though, an upsert from the browser needs rules that let them read the table, and the line with Maria's address in it is something your app should never print to a visitor at all.

What does "duplicate key value violates unique constraint" mean?

It means the value you tried to save is already in a column that takes each value once, so Postgres refused the whole row. Nothing was written, and the row that was there first is unchanged.

Think of the table as the guest list at a door, one line per name. A unique constraint is the doorman's rule that a name goes on the list once. Your app walked up with a name already written there, and he turned it away.

Each part of the error says one thing:

The wording is Postgres's own. The code that raises it adds the Key ... already exists line only when it has a description of the key to give, and a section further down covers when it has none.

Which column caused it?

The name in quotes tells you, because Postgres builds it from the table and the columns unless somebody chose a different one. Its naming code gives three shapes:

The message says "unique constraint" for all three. To see exactly what a name covers, run this in the SQL Editor with the name from your message:

select tablename, indexdef
from pg_indexes
where indexname = 'waitlist_email_key';

indexdef comes back as something like CREATE UNIQUE INDEX waitlist_email_key ON public.waitlist USING btree (email), and the columns in the brackets are the ones that may not repeat. A rule on two columns, such as (team_id, email), lets the same address join two teams and refuses it twice in one.

"Key (id)=(1) already exists" when you never sent an id

This one means the table's id counter is behind the rows already in it, so it handed out a number that is taken. Your app sent no id. Postgres took the next number from the counter, and a row with that number was already there.

On the guest list, the doorman also hands out numbered wristbands from a roll. Someone copied the first two hundred guests in by hand, wristband numbers included, and nobody wound the roll on. It still starts at 1.

A column that numbers itself takes each number from a sequence, Postgres's word for that counter. Postgres's page on identity columns says such a column "does not guarantee uniqueness", and gives this case as one of the reasons: values "could be inserted manually into the identity column". Rows arrive with their numbers already filled in from a CSV import that includes the id column, a copy from another project, or a seed script. The counter never hears about any of them. Supabase's troubleshooting page on id sequences covers this exact message on a _pkey, and its repair is a setval as well.

The repair is one statement. Put your own table and column in place of orders and id; the table is the part of the name before _pkey:

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

pg_get_serial_sequence finds the counter behind the column, and Postgres's documentation says it works for both kinds: "If the column is an identity column, the associated sequence is the sequence internally created for that column." Then setval with two arguments sets the counter so that "the next nextval will advance the sequence before returning a value". The next new row gets the highest id plus one. If Postgres answers that the relation does not exist, the table is spelled differently, has a capital letter or sits in another schema, and that error has a post of its own.

A restore from pg_dump output does not leave you here. Its documentation lists what a data dump carries: "Table data, large objects, and sequence values are dumped." Each counter comes back with its rows.

The three ways to handle a duplicate

Decide what should happen when the value is already there: refuse the new row and say so, let it replace the old row, or stop sending it in the first place.

Refuse it, in your own words. This fits when a second row would be a mistake: an address already registered, a username already taken. The insert fails, your app reads the code, and the person sees a sentence you wrote:

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

if (error?.code === '23505') {
  setMessage('That username is taken. Try another one.')
} else if (error) {
  setMessage('Something went wrong. Please try again.')
}

Let it replace the old row. This fits when the newest version should win: a settings row per user, one cart per session. That is an upsert, and the next section is about when it goes wrong.

Stop sending it. Sometimes the second row was never meant to exist, and it arrives from one of these:

• a button that sends the form again when pressed again. Disable it until the first request answers.
• a retry after a slow response, when the first attempt had gone through
• two writers for one row, such as a trigger that creates each new user's profile and app code that inserts the same profile after sign-up

The trigger case is worth knowing by sight. A trigger that copies new users into a table of yours fails with this error when the value it copies is already there, and Supabase reports the whole sign-up as "Database error saving new user". The 23505 itself is in the Postgres logs, and that post shows where.

Should you use upsert?

Use it when the new row should replace the old one, and check three things before you rely on it.

.upsert() in supabase-js is an insert that does something else when the row exists. Its source puts it in two lines: "If true, duplicate rows are ignored. If false, duplicate rows are merged with existing rows." That is the ignoreDuplicates option, and Postgres runs either version as insert ... on conflict.

1. onConflict has to name the column from your error.PostgREST's documentation says: "By default, upsert operates based on the primary key columns". An upsert of { email } into a table whose primary key is id looks for a clash on id, finds none because the id is new, inserts, and meets the rule on email with the same 23505. Postgres's reference for INSERT says the alternative action is taken "if an arbiter constraint or index specified by conflict_target is violated". A clash on any other column refuses the row as before.

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

2. That column needs a unique rule of its own. Without one, Postgres has nothing to match and answers there is no unique or exclusion constraint matching the ON CONFLICT specification, code 42P10 (the line that raises it).

3. From the browser, Row Level Security reads more of your rules. Row Level Security is the set of per-row rules Supabase checks on every request made with your public key. For an upsert, Postgres's reference for policies says "SELECT permissions are required on the relation, and the rows proposed for insertion are checked using the relation's SELECT policies." When the row exists and the upsert merges, the existing row is checked against the table's update rules as well. A row that fails either check ends the request with new row violates row-level security policy.

So on the waitlist, an upsert from the browser fails until the table has a read rule a visitor passes. A read rule a stranger passes for any address is a rule that lets strangers read your list. At the door, writing over a line means being shown the list first. For a table like that, refuse the duplicate instead, or use the function in the next section.

Is it safe to show this error to your users?

No. Show a sentence of your own and keep the raw error in your logs, for two reasons: the detail line can carry the value back, and the error confirms the value exists whatever you print.

The detail line repeats the value. Key (email)=(maria@example.com) is harmless on Maria's own screen and very different in a screenshot posted to a support forum, in an error tracker, or on the screen of whoever typed someone else's address into your form. Postgres already guards it. The function that builds the line (BuildIndexValueDescription) checks first: "if RLS is enabled for the relation. If so, return NULL to avoid leaking data." A request made with your public key to a table with Row Level Security on gets "details": null.

Read the other way round, the line tells you about the table. If your browser console shows Key (...)=(...) for a request your app made with the public key, Row Level Security is off on that table. The one exception is a request your app sends through server code holding the secret key, which skips the rules entirely. null proves less: Postgres also drops the line when the caller cannot read every column in the key. Turning Row Level Security on for every table covers the switch and the first rule.

A table with the rules off answers a stranger as readily as it answered you. Our free scan reads your live app with the same public key a visitor gets, reports which tables hand back rows, and needs no account.

The error answers a question you may not want answered. With the detail line gone, a 409 still tells whoever sent the row that the value is already there. Postgres's page on row security says unique constraints "always bypass row security to ensure that data integrity is maintained", and asks you to design tables and rules so these checks do not turn into a covert channel. On the waitlist, anyone can learn whether an address is on your list by trying to add it. The doorman never reads the name out, and turning someone away still tells the queue it was there.

If that matters for your list, give the browser the same answer either way. A function in the database can add the address, skip it if it is already there, and return nothing in both cases:

create function public.join_waitlist(new_email text)
returns void
language sql
security definer
set search_path = ''
as $$
  insert into public.waitlist (email)
  values (new_email)
  on conflict (email) do nothing;
$$;

Your app calls supabase.rpc('join_waitlist', { new_email: email }). security definer runs the insert as the function's owner, so visitors need no rule on the table at all, and the rule that let them insert can go, or the old route stays open beside the new one. What "Database error saving new user" says about security definer explains what it hands over, and calling a Supabase function covers matching the argument names.

Before you change how your app writes

Keep a copy of the database from before you switch an insert to an upsert or reset a counter, and hear about it when a table starts answering strangers.

Reeve Care keeps a copy of your Supabase database:

• an encrypted copy on a schedule, on Reeve's own storage in the EU, outside your Supabase account
• every table with its rows as they were before a merge wrote over them
• each table's id counter put back with its rows on a restore, so the next new row gets a free number
• each copy verified before it counts, by counting the rows in every table
• your uploaded files as well, once you connect a Storage credential
• a one-click restore, with a safety copy of the current state taken first
• everything Monitor does

Reeve Monitor keeps checking from outside:

• whether your app is up, checked every minute
• the full security check every hour, on up to three apps
• a message when a result changes, so a table that starts answering strangers after a deploy reaches you without you going to look
• a monthly report, in plain language, of what it saw

What to do now

Start with the name in quotes, because it says which rule refused the row and which column that rule guards. Before you run a setval, an upsert or anything else that rewrites rows people depend on, keep a copy of the database from before the change.