Postgres SERIAL sequences

Published by bolmaster2

Don’t forget to update your SERIAL sequences

When copy-pasting sql, which I do sometimes for development purposes (thanks to postico 🙇). Then the next value of the SERIAL sequence (Yes, everyone is not using UUID for everything 😎) is not updated. Which will make all INSERT queries to fail.

This is a command to set a new value to the sequence, it’s getting the max value of id on the users table, which will make sure there’ll be no collisions.

SELECT setval('users_id_seq', (SELECT max(id) FROM users));

This would work as well, if you can’t use the max function for some reason:

SELECT setval('users_id_seq', (SELECT id FROM users ORDER BY id DESC LIMIT 1));

To see what is the going to be the next value of the sequence you can use this function, a bit weird that it’s called currval. I would expect the function nextval to do this, but that one is not idempotent, it’s incrementing the value as well as returning it.

SELECT currval('users_id_seq');

References