# duplicate key value violates unique constraint after importing data

> After a CSV or SQL import, inserts fail with a duplicate key on the primary key. Reset the id sequence to the table's highest value, for one table or the whole schema.

Source: https://supahost.app/kb/duplicate-key-sequence · Updated 2026-11-11

## What you see

The first inserts after an import fail, even though you didn't send an id:

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

Through the Data API, supabase-js returns HTTP 409 with `"code": "23505"`. If you keep retrying, the id in `DETAIL` goes up by one each time.

## Why it happens

`serial`, `bigserial` and identity columns get their next value from a sequence, a counter that lives separately from the table. When rows are loaded with explicit ids (a CSV import, `insert ... values (1, ...)`, or a data-only dump without sequence values), the table fills up but the sequence stays where it was, often at 1. New inserts then ask the sequence for an id that's already taken.

## How to fix it

1. Find the sequence and compare it with the table:

   ```sql
   select pg_get_serial_sequence('public.invoices', 'id') as seq;

   select max(id) from public.invoices;
   select last_value, is_called from public.invoices_id_seq;
   ```

2. Move the sequence past the highest id. Passing `false` means the next `nextval` returns exactly this number:

   ```sql
   select setval(
     pg_get_serial_sequence('public.invoices', 'id'),
     coalesce((select max(id) from public.invoices), 0) + 1,
     false
   );
   ```

3. If you imported many tables, reset every sequence-backed column in `public` in one go:

   ```sql
   do $$
   declare
     r record;
   begin
     for r in
       select n.nspname as schema_name, c.relname as table_name, a.attname as column_name,
              pg_get_serial_sequence(format('%I.%I', n.nspname, c.relname), a.attname) as seq
       from pg_class c
       join pg_namespace n on n.oid = c.relnamespace
       join pg_attribute a on a.attrelid = c.oid and a.attnum > 0 and not a.attisdropped
       where n.nspname = 'public' and c.relkind = 'r'
     loop
       if r.seq is not null then
         execute format(
           'select setval(%L, coalesce((select max(%I) from %I.%I), 0) + 1, false)',
           r.seq, r.column_name, r.schema_name, r.table_name
         );
       end if;
     end loop;
   end $$;
   ```

4. Retry the insert. It should get the next free id.

## Preventing it next time

- When importing a CSV whose ids don't matter to other tables, leave the id column out and let Postgres assign new ones.
- Use identity columns (`id bigint generated always as identity primary key`) for new tables. With `always`, Postgres rejects explicit ids unless you write `overriding system value`, which makes the mismatch harder to create by accident.
- After any bulk load with explicit ids, run step 3 as part of the import script.
- If your table uses `uuid` primary keys with `default gen_random_uuid()`, there's no sequence and this problem can't happen.

## On SupaHost

- Run these queries in Studio's SQL editor (*Dashboard → your project → **Database** tab → Open Studio*), with MCP `execute_sql`, or with psql through the SSH tunnel on Business.
- Full pg_dump restores (including SupaHost's own exports) carry sequence values along with the data. The problem usually comes from loading rows with explicit ids some other way, such as a CSV upload in Studio or a hand-written `insert`.

## Related

- [Importing data (CSV, SQL dumps)](/docs/database/import-data)
- [Why are there gaps in my id sequence?](/kb/sequence-gaps)
- [Tables, columns and data](/docs/database/tables)