Postgres SERIAL sequences
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');