Tuesday, 12 January 2010

Updating the nextval of all sequences in PostgreSQL

If you've been doing SQL level inserts for testing purposes, then when you go to use the sequences then they will have a nextval that already exists in your table.

So below is a script which updates the nextval for all fields that were created using bigserial/serial:
/* Updates all the sequences to have a next value of max+1 excluding the list passed */
CREATE OR REPLACE FUNCTION fn_fixsequences(excludes text) RETURNS integer AS
$BODY$
DECLARE
themax BIGINT;
mytables RECORD;
num integer;
BEGIN
 num := 0;
 FOR mytables IN 
  select relname, ns.nspname, a.attname, pg_get_serial_sequence(c.relname, a.attname) as seq
  FROM pg_catalog.pg_attribute a INNER JOIN 
   pg_catalog.pg_class c ON c.oid=a.attrelid
    inner join pg_catalog.pg_attrdef d
    on d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef
       LEFT JOIN pg_catalog.pg_namespace ns ON ns.oid = c.relnamespace
  WHERE 
    pg_catalog.pg_table_is_visible(c.oid)
   and a.attnum > 0 AND NOT a.attisdropped  and atttypid=20
   and relname not like 'pg_%' and relname not like excludes
   and ns.nspname not in ('information_schema', 'pg_catalog')
   and c.relkind='r'
   and not pg_get_serial_sequence(c.relname, a.attname) is null
 LOOP
      EXECUTE 'SELECT MAX('||mytables.attname||') FROM '||mytables.nspname||'.'||mytables.relname||';' INTO themax;
      IF (themax is null OR themax < 0) THEN
       themax := 0;
      END IF;
      themax := themax +1;
      EXECUTE 'ALTER SEQUENCE ' || mytables.seq || ' RESTART WITH '||themax;
      num := num + 1;
  END LOOP;
 
  RETURN num;
 
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
COMMENT ON FUNCTION fn_fixsequences() IS 'Updates all the sequences to have a next value of max+1 excluding the list passed';

2 comments:

  1. Dear Chris,

    Thank you very much for this very useful script. It helped me a lot on migrating a huge SQL server application to Postgres.

    I detected, that the filter condition " and atttypid=20" does not work on my system (version 8.4). The function works great without the condition.

    Kindest regards,
    Lothar in Cologne
    BTW: I am planning to migrate to Sydney area

    ReplyDelete
  2. Hi,

    USAGE TIP: set search path to the schema you aim at before executing the function.
    the scripts only acts on relations in the search path.

    ReplyDelete