How to fix postgres sequences that are causing integrity errors.

Save this to a sql file:

SELECT 'SELECT SETVAL(' ||quote_literal(quote_ident(S.relname))|| ', MAX(' ||quote_ident(C.attname)|| ')+1) 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;

run the sql file like this:

psql -Atq my_database < reset.sql > tmp.sql
psql my_database < tmp.sql

This entry was posted in postgres. Bookmark the permalink.

Comments are closed.