If you use a serial datatype, it simply says "the *default* behavior is
to use the next value of sequence a". So you can insert 1, 2,
10,204,492 into the column just fine. However, by inserting data into
something you want to auto-increment, you can run into non-uniqueness.
(Like you were). So most people tend to leave the id field as
completely null. Setting the value is only for loading data, or data
that already has something as a PK.
The command to set it is "select pg_catalog.setval('sequence_name',
value)". Check the docs on sequences for more info.
On Saturday, July 12, 2003, at 02:36 AM, Chad N. Tindel wrote:
>>> --------------------
>>> drop table A;
>>> create table A (
>>> id SERIAL PRIMARY KEY,
>>> foo int default 5,
>>> bar int default 10
>>> );
>>>
>>> insert into A (id, foo, bar) values (1, 1, 1);
>>> insert into A (id, foo, bar) values (2, 2, 2);
>>> insert into A (id, foo, bar) values (3, 3, 3);
>>> insert into A (id, foo, bar) values (4, 4, 4);
>> A serial data type will allow you to input values into it, but the
>> counter is still at 0. That's why your first update statement's
>> nextval outputs "1". It's not showing what was already inserted, it's
>> showing what would have been. So at this point you need to set the
>> current value of id at 4.
>
> I wasn't quite able to figure out what the solution to my problem
> should be...
> Are you saying that a serial type isn't smart enough to realize that I
> just
> inserted the values 1, 2, 3, 4, and to figure out that if I ask it to
> self-generate the next one that it should return a 5? I have to tell
> it that
> the next value it should return is 5?
>
> Maybe I'm just using the wrong feature of postgres... is there some
> way to
> get the same behavior as a mysql auto_increment primary key column?
>
>
>> Nextval *sets* the sequence at N+1.
>
> OK... didn't know that. How do I set the sequence to an arbitrary
> value X?
>
> Thanks for the help! Hopefully I'll get the hang of this soon.
>
> Chad
>