How to fix a sequence when it goes out of sync in Postgres

Was copied from https://arctype.com/blog/postgres-sequence/ https://web.archive.org/web/20230928041745/https://arctype.com/blog/postgres-sequence/

Method 2: Fixing all your sequences with one script

If you wanted to fix all your sequences with one query, you could use the following script coming from the official Postgres Wiki:

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

Using a single query to fix all sequences This query returns the set of queries required to fix each of your sequences when executed. As stated in the Wiki, you should use this query as follows:

Save the query in a fix_sequences.sql file.
Run the query contained in the fix_sequences.sql file and store the result in a temp file. Then, run the queries contained in the temp file. Finally, delete the temp file. You can achieve this with the following three commands:

bash

psql -Atq -f fix_sequences.sql -o temp

psql -f temp

rm temp

Fixing the series and then cleaning up the temp file