How to reset PostgreSQL's primary key sequence for all tables when it falls out of sync?

Question

After an import to a database through a tool like Postico, or other, the ID sequence for tables fall out of sync.

I've learnt that I can fix this by running

SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), (SELECT MAX(id) FROM table_name)+1);

but how would I do this for all tables, without having to run the same command for each?

Navid Khan · Accepted Answer · 2021-06-11 11:24:33Z

2

I've actually found an answer.

Create a file reset.sql with this as the content:

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;

Run this file as

psql -h yourdb.yourhost.com -U mysecretuser -Atq -f reset.sql -o temp

Then run the output as

psql -h yourdb.yourhost.com -U mysecretuser -f temp

And finally

rm temp

edited Jun 11, 2021 at 11:24

answered Aug 19, 2019 at 14:49

Navid Khan

1,1851 gold badge13 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kunambi Over a year ago

Running the first command, I get the error: reset.sql: No such file or directory

Navid Khan Over a year ago

@kunambi You have to create the file with the content shared at the top of the answer.

Collectives™ on Stack Overflow

How to reset PostgreSQL's primary key sequence for all tables when it falls out of sync?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related