Search code examples
postgresqlsequence-sql

In Postgresql, Sequence numbers set to start at 1, but is actually returning new records with id of 2?

Using setval('sequence',1) sets the start value of the sequence to 1. But when a record is inserted, the first 'sequence' number is actually 2.

How do I get the actual first record to have a sequence number of 1?

Solution

  • From the fine manual:

    setval
    Reset the sequence object's counter value. The two-parameter 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. [...]

    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

    So calling setval('sequence', 1) sets the sequence's current value to 1 and the next value will be 2. You probably want the three argument form of setval:

    setval('sequence', 1, false)

    so that the is_called flag on the sequence will be false and nextval('sequence') will be 1. Also note that the default value for columns bound to sequences is nextval('sequence').