Fixing Sequences

From PostgreSQL Wiki

A common problem when copying or recreating a database is that database sequences are not updated just by inserting records in the table that sequence is used in. If you want to make your sequences all start just after whatever values are already there, it's possible to do that for most common configurations like this:

SELECT  'SELECT SETVAL(\'' ||S.relname|| '\', MAX(' ||C.attname|| ') ) FROM ' ||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;

Usage would typically work like this:

• Save this to a file, say 'reset.sql'
• Run the file and save its output in a way that doesn't include the usual headers, then run that output. Example:

psql -Atq -f reset.sql -o temp
psql -f temp
rm temp

There are a few limitations to this snippet of code you need to be aware of:

• It only works on sequences that are owned by a table