Debugging Postgres Sequences

How to determine if your postgres sequences are working.

Sequences are used to auto increment an identifying column in a table. Usually this happens on insert, but occasionally its done before the record is inserted. Sometimes you’ll get constraint errors and will wonder what is not working with your sequences.

In order to debug it, try getting the sequence from the serial sequence method in Postgres:

SELECT currval(pg_get_serial_sequence('examples', 'id'));

If this doesn’t work, try querying the sequence directly:

SELECT last_value from examples_id_seq;

You may need to do that if your session hasn’t established the currval for the sequence. That is established per session.

To get the next value, run the nextval command:

SELECT nextval(pg_get_serial_sequence('examples', 'id'));

This should result in the right incrementing of the id. If for some reason you determined that your id is not starting at the right number, this usually happens from a migration to a new db table, you’ll need to alter the sequence on the table to the right starting point.

ALTER SEQUENCE examples_id_seq RESTART WITH 100;

You could also restart the sequences and update the existing table:

ALTER SEQUENCE examples_id_seq RESTART;
UPDATE examples SET id = DEFAULT;

Some references to follow up on: * Neil Conway’s sequence page * StackOverflow’s Error: currval of sequence is not yet defined in this session * Postgres alter sequences

Any other tips, leave some comments below.