Обсуждение: setval('myfunsequence', 0)

The subject statement does not work for automagically generated sequences (minvalue is 1 by default, it seems.)  I need
toreset a sequence to where nextval('myfunsequence') is 1, and the only way to do it (I think) is to
setval('myfunsequence',0). 

I can just create this particular sequence with minvalue 0 start 1, but I am wondering what is the downside to this
beingthe default?  Alternatively, is there a way to make setval accept a value that is $start - $increment as seems to
happenon creation? 

Visual Aids Follow...

test=# create sequence myfunsequence;
CREATE
test=# select nextval('myfunsequence');
 nextval
---------
       1
(1 row)

test=# select nextval('myfunsequence');
 nextval
---------
       2
(1 row)

test=# select setval('myfunsequence',0);
ERROR:  myfunsequence.setval: value 0 is out of bounds (1,9223372036854775807)
test=# select setval('myfunsequence',1);
 setval
--------
      1
(1 row)

test=# select nextval('myfunsequence');
 nextval
---------
       2
(1 row)

test=# drop sequence myfunsequence;
DROP
test=# create sequence myfunsequence minvalue 0 start 1;
CREATE
test=# select nextval('myfunsequence');
NOTICE:  myfunsequence.nextval: sequence was re-created
 nextval
---------
       1
(1 row)

test=# select nextval('myfunsequence');
 nextval
---------
       2
(1 row)

test=# select setval('myfunsequence',0);
 setval
--------
      0
(1 row)

test=# select nextval('myfunsequence');
 nextval
---------
       1
(1 row)

Thanks!!

Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
iharding@tpchd.org
(253) 798-3549

On Fri, 6 Dec 2002, Ian Harding wrote:

> The subject statement does not work for automagically generated sequences (minvalue is 1 by default, it seems.)  I
needto reset a sequence to where nextval('myfunsequence') is 1, and the only way to do it (I think) is to
setval('myfunsequence',0). 
>
> I can just create this particular sequence with minvalue 0 start 1, but I am wondering what is the downside to this
beingthe default?  Alternatively, is there a way to make setval accept a value that is $start - $increment as seems to
happenon creation? 
>

Not true.  Take a look at:
http://developer.postgresql.org/docs/postgres/functions-sequence.html

And note near the bottom, that you can setval with a third boolean value,
like so:

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

and the next nextval() will return 1

Argh!  Sorry.  I should RTFM!

>>> "scott.marlowe" <scott.marlowe@ihs.com> 12/06/02 07:53AM >>>
On Fri, 6 Dec 2002, Ian Harding wrote:

> The subject statement does not work for automagically generated sequences (minvalue is 1 by default, it seems.)  I
needto reset a sequence to where nextval('myfunsequence') is 1, and the only way to do it (I think) is to
setval('myfunsequence',0). 
>
> I can just create this particular sequence with minvalue 0 start 1, but I am wondering what is the downside to this
beingthe default?  Alternatively, is there a way to make setval accept a value that is $start - $increment as seems to
happenon creation? 
>

Not true.  Take a look at:
http://developer.postgresql.org/docs/postgres/functions-sequence.html

And note near the bottom, that you can setval with a third boolean value,
like so:

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

and the next nextval() will return 1

"Ian Harding" <ianh@tpchd.org> writes:
> I can just create this particular sequence with minvalue 0 start 1, but I am wondering what is the downside to this
beingthe default?  Alternatively, is there a way to make setval accept a value that is $start - $increment as seems to
happenon creation? 

There's a separate bit in the sequence state that causes it not to
advance on first call; this allows the initial state to be $start
and not $start - $increment (which might underflow).

In recent PG versions you can manipulate the no-advance flag with
a 3-parameter setval() call.

            regards, tom lane