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';