duplicate key value violates unique constraint after importing data

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:

   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:

   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:

   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)
• Why are there gaps in my id sequence?
• Tables, columns and data