Re: going crazy with serial type

> I've been through the documentation, but for some reason, no one seems
> to think a programmer would ever want functionality like mysql's
> AUTO_INCREMENT, so as far as I can tell, it's not even addressed.

Actually, it has it's own section in the manual:

http://www.us.postgresql.org/users-lounge/docs/7.1/user/datatype.html#DATATY
PE-SERIAL

As well as a two questions in the FAQ:

http://www.us.postgresql.org/docs/faq-english.html#4.15.1
http://www.us.postgresql.org/docs/faq-english.html#4.15.2

And even more information in the online book:

http://www.ca.postgresql.org/docs/aw_pgsql_book/node75.html
http://www.ca.postgresql.org/docs/aw_pgsql_book/node76.html

> 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"

Just a guess here, but it seems that '' is being cast into an integer as a
0, therefore your inserts end up as:

insert into mytable (0, 1, 2);
insert into mytable (0, 5, 6);

What you want to do is either leave out the serial field:

insert into mytable (a, b) values (1, 2);
insert into mytable (a, b) values (5, 6);

Or explicitly give the serial a NULL value:

insert into mytable (NULL, 1, 2);
insert into mytable (NULL, 5, 6);

Hope this helps,

Greg