Re: going crazy with serial type

On Thu, 31 Jan 2002, Cindy wrote:

> I do !NOT! understand how the SERIAL type works.  I want something
> like I had in mysql that would generate new, unique numbers, each time
> I added a new record.  I want something that sits down, shuts up, and
> just GIVES me the number on demand.  (I also want a guarantee that the
> unique number is consecutive, and is never zero or negative.)  In short,
> I want the AUTO_INCREMENT behavior.

You won't get that.  serial (and sequences) are guaranteed to give numbers
that haven't shown up in the sequence (note: you can still get duplicates
if you set values yourself, you can get around this with triggers - there
was a recent example on one of the mailing lists I believe) not
consecutive numbers due to concurrency concerns (if one transaction asks
for a number and then a second also asks for a number, you need to wait
for transaction one to commit or rollback before you can give the second a
number if you want to guarantee consecutive numbers).

> But so far it's been one giant headache.  Tell me, how do I insert
> new records into a table *without* specifying an actual number?  In
> mysql it's just an empty field.  I have been unable to determine how
> to do this in psql other than to ascertain it certainly isn't through
> the same way.
You don't insert a value into the field (see below).  There's a difference
between inserting an empty value or even a NULL and not inserting a value.

> I'd appreciate any help.  I basically have a table:
> create table mytable (mytable_id serial, a int, b int);
> and
>
> insert into mytable ('', 1, 2); is accepted but then following
> insert into mytable ('', 5, 6); etc, is rejected due to "duplicate key"
insert into mytable (a,b) values (1,2);
insert into mytable (a,b) values (5,6);