setval and nextval for PostgreSQL Sequences

Sequences in PostgreSQL allow us to automatically insert the next value in a sequence within our applications.

You may find yourself using sequences for things like primary key IDs, or maybe you have another use for them altogether.

In this article, we are going to look at how you can get and set values to/from a sequence.

Getting The Next Value in a Sequence

If we have a sequence within our database, we can call the nextval function, passing in the name of our sequence in order to get the next value in our sequence:

=> select nextval('my_table_seq');
1

Setting the Next Value in a Sequence Manually

If we need to set the next value in a sequence for any reason, such as a failed database migration for example, then we can use the setval function in order to manually set this value:

=> select setval('my_table_seq', 5, true);
=> select nextval('my_table_seq');
6

=> select setval('my_table_seq', 10, false);
=> select nextval('my_table_seq');
10

In the above example, we’ve set the sequence my_table_seq to the value 5 and the third argument is the is_called boolean argument.

If we set is_called to true, then nextval for our sequence will return the next value in the my_table_seq sequence. We can see this in action as we’ve set it to 5 originally, but the nextval call immediately after this returns 6.

If we set this to false, then the next call to nextval will return the value we’ve set. In the second example, we’ve set this to 10 and when we’ve called nextval it then returns 10.