> 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