Setting a Database Sequence Correctly

Like you, I am constantly learning. This post is for my own reference as much as it is for others. In fact, often my posts are for my future self!

I am not a database administrator or the son of a database admin. However, I consider myself proficient when working with various relational database management systems.

Sooooooo, I had created a table and a primary key sequence for it in Postgresql. However, when I would insert a record in the table, the initial id for the first record insert was 2 and not 1!. So, how to fix this?

My first thought was to set the sequence start and minimum values to 0. So, I did the following in SQL:

SELECT setval('mytable_id_seq', 0); -- set to 0

This resulted in the following error:

ERROR:  setval: value 0 is out of bounds for sequence "prayernote_id_seq" (1..9223372036854775807)
SQL state: 22003

Okay, my next step was to set the sequence start and minimum values to 0. So, I did the following:

ALTER SEQUENCE mytable_id_seq
MINVALUE 0
START 0;

Then, I was able to set the sequence value to 0 without an error, so that the id for the first inserted record will be 1:

SELECT setval('mytable_id_seq', 0); -- set to 0

Now, when I inserted a record in the table, the id for the first record insert was 1 and not 2. Yet, this seemed like a hack and not a standard practice.

After some Googling, I found that I could have simply done the following to insure that the initial id for the first record insert was 1:

SELECT setval('seq', 1, false);

Note the third parameter in this setval call. Per the Postgresql documentation, the two-parameter setval form sets the sequence’s last_value field to the specified value and sets its is_called field to true, meaning that the next nextval will advance the sequence before returning a value. In the three-parameter form, is_called, the third parameter, may be set either true or false. If it’s set to false, the next nextval will return exactly the specified value, and sequence advancement commences with the following nextval. For example:

SELECT setval('foo', 42); -- Next nextval will return 43
SELECT setval'foo', 42, true); -- Same as above
SELECT setval('foo', 42, false); -- Next nextval will return 42

Therefore, using the three-parameter form setting is_called to FALSE makes sure that the nextval is 1 instead of 2. So, what I should have done from the start was:

SELECT setval('mytable_id_seq', 1, false);

Live and learn, sign.