Re: [GENERAL] Am I using the SERIAL type properly?

[Jeffrey Melloy]

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



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend