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
– Deepak Mahakale, Commented Oct 23, 2017 at 6:53

Vao Tsun · Accepted Answer · 2017-10-23 09:31:40Z

6

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, 2017 at 9:31

answered Oct 23, 2017 at 7:12

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Nell David S. Over a year ago

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

Vao Tsun Over a year ago

hm - maybe Postico does not understnd DO statement?.. _r record is declared...

Nell David S. Over a year ago

hmmm this is weird it's also giving me the same error with psql

Vao Tsun Over a year ago

@NellDavidS. sorry - corrupted code. fixed a bug - now should work

John Brodie Over a year ago

This still works just fine on 12.x, for anyone finding this in 2020.

|

Collectives™ on Stack Overflow

Postgres: Update primary key sequence for all tables

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related