Postgres: Update primary key sequence for all tables

Question

I've manually imported all the data from production to my development server but I'm having this error. I've also read here that fixes this issue but is only limited to a single table. I've imported around 10+ tables along with their data. This is the error:

PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "influences_pkey" DETAIL: Key (id)=(1) already exists. : INSERT INTO "influences" ("name", "created_at", "updated_at") VALUES ($1, $2, $3) RETURNING "id"

So just set the primary key to start accordingly. You will be just updating the start point of primary key from 1 to 21 and next time db will start from 21 — Deepak Mahakale, Oct 23 '17 at 06:53

Vao Tsun · Accepted Answer · 2017-10-23T09:31:40.330

4

here is plpgsql to reset all sequences (run in pgadmin or psql or any other client):

do 
$$
declare
 _r record;
 _i bigint;
 _m bigint;
begin
  for _r in (
    SELECT relname,nspname,d.refobjid::regclass, a.attname, refobjid
    FROM   pg_depend    d
    JOIN   pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
    JOIN pg_class r on r.oid = objid
    JOIN pg_namespace n on n.oid = relnamespace
    WHERE  d.refobjsubid > 0 and  relkind = 'S'
   ) loop
    execute format('select last_value from %I.%I',_r.nspname,_r.relname) into _i;
    execute format('select max(%I) from %s',_r.attname,_r.refobjid) into _m;
    if coalesce(_m,0) > _i then
      raise info '%',concat('changed: ',_r.nspname,'.',_r.relname,' from:',_i,' to:',_m); 
      execute format('alter sequence %I.%I restart with %s',_r.nspname,_r.relname,_m+1);
    end if;
  end loop;

end;
$$
;

or use any other solution proposed at How to reset postgres' primary key sequence when it falls out of sync?

edited Oct 23 '17 at 09:31

answered Oct 23 '17 at 07:12

Vao Tsun

37,644
8
70
98

Thanks for your answer but it doesn't work, on mine at least. Using Postico and it's giving me this error: ERROR: schema "_r" does not exist LINE 1: SELECT coalesce(_r.nspname,'.',_r.relname' ',_i,' ',_m) ^ QUERY: SELECT coalesce(_r.nspname,'.',_r.relname' ',_i,' ',_m) CONTEXT: PL/pgSQL function inline_code_block line 18 at RAISE – Nell David S. Oct 23 '17 at 08:41
hm - maybe Postico does not understnd `DO` statement?.. `_r record` is declared... – Vao Tsun Oct 23 '17 at 09:09
hmmm this is weird it's also giving me the same error with psql – Nell David S. Oct 23 '17 at 09:26
@NellDavidS. sorry - corrupted code. fixed a bug - now should work – Vao Tsun Oct 23 '17 at 09:32
@NellDavidS. and thank you - fixed it in original answer – Vao Tsun Oct 23 '17 at 09:38
1

This still works just fine on 12.x, for anyone finding this in 2020. – John Brodie Aug 06 '20 at 16:08

Postgres: Update primary key sequence for all tables

1 Answers1

Linked