When PostgreSQL Table Sequences Get Out Of Sync

Have you ever found yourself in a situation where you entered a primary key when inserting a row into the database, and thereby invalidated the primary key sequence? I have.

Single Table

If you need to only update one table, the following should work:

1. Get the max primary key in the table.
2. Run the following query:

ALTER SEQUENCE <table-name>_id_seq RESTART WITH <max-id>;

3. That should take care of things.

Update All Tables's Primary Keys

It’s always a good idea to make sure all tables are in running order, though. In order to make sure the entire database's sequences are up-to-date, run the following script:

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
   	IF EXISTS (SELECT 0 FROM pg_class WHERE relkind = 'S' AND relname = _r.nspname || '.' || _r.relname)
   	THEN
	    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 IF;
  end loop;
end;
$$
;