Re: SERIAL type fields

On Thu, 24 Apr 2003, Andrew Ayers wrote:

> When you create the table from new, it does this - but after that, the
> sequence table says that the minimum value for the sequence is "1" - and
> not zero - so that when you add records to the table the sequence is
> used on, the first record has a value of "2" in that field when it is added.
>
> I tried to reset the minimum value to "0" - but it wouldn't let me. Does
> anyone know of how you do this, without having to DROP the table and
> sequence, and re-creating them? Is there some kind of ALTER TABLE
> command, or possibly something the database setup, that would allow me
> to alter this behavior?

Sure, take a look here:

http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=functions-sequence.html

Notice near the bottom, you get this:

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. In the three-parameter form, is_called
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

That last one is what you want.