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

Question

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?

mu is too short · Accepted Answer · 2016-12-14 19:43:34Z

4

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').

answered Dec 14, 2016 at 19:43

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related