Fix Postgresql sequences ¶

1. Produce the list of required statements with this query:

SELECT
  'SELECT SETVAL(' ||quote_literal(S.relname)||
  ', MAX(' ||quote_ident(C.attname)||
  ') ) FROM ' ||quote_ident(T.relname)|| ';'
FROM
  pg_class AS S
  ,pg_depend AS D
  ,pg_class AS T
  ,pg_attribute AS C
WHERE
  S.relkind = 'S'
  AND S.oid = D.objid
  AND D.refobjid = T.oid
  AND D.refobjid = C.attrelid
  AND D.refobjsubid = C.attnum
ORDER BY S.relname;

Example:

echo "SELECT 'SELECT SETVAL(' ||quote_literal(S.relname)|| ', MAX(' ||quote_ident(C.attname)|| ') ) FROM ' ||quote_ident(T.relname)|| ';' FROM pg_class AS S ,pg_depend AS D ,pg_class AS T ,pg_attribute AS C WHERE S.relkind = 'S' AND S.oid = D.objid AND D.refobjid = T.oid AND D.refobjid = C.attrelid AND D.refobjsubid = C.attnum ORDER BY S.relname;" | psql mydb > fix_sequences.sql

Sample ouput:

SELECT SETVAL('archive_attachment_id_seq', MAX(id) ) FROM archive_attachment;
SELECT SETVAL('archive_tipotipologiaattachment_id_seq', MAX(id) ) FROM archive_tipotipologiaattachment;
SELECT SETVAL('auth_group_id_seq', MAX(id) ) FROM auth_group;
SELECT SETVAL('auth_group_permissions_id_seq', MAX(id) ) FROM auth_group_permissions;
SELECT SETVAL('auth_permission_id_seq', MAX(id) ) FROM auth_permission;
SELECT SETVAL('auth_user_groups_id_seq', MAX(id) ) FROM auth_user_groups;
...

2. Execute all statements

Example:

psql mydb < fix_sequences.sql