Re: problems with unique key on table with serial

chris G�nther <guenther@uscreen.de> writes:
>     CREATE TABLE "tblserver" (
>     "ID_Server" oid DEFAULT nextval('tblserver_id_server_seq'),
>     ...

>     INSERT INTO tblserver ("ID_Server", "Svr_Name", "IP_Address",
>     "Location", "Description")
>     VALUES (1, 'slughammer', '24.3.19.73', 'slugmania',
>     'db-design by chris') \g

> Now, when I try to insert a second dataset in the table from my
> Web application I get the error-message:

> Warning: PostgreSQL query failed: ERROR:
> Cannot insert a duplicate key into unique index tblserver_pkey in
> /sitebuilder/_inc/fnc_server_adm.php on line 178

You inserted a row with an explicit specification of the ID_Server
column.  That's fine, but it didn't advance the sequence counter.
So your first try to insert something without a specified ID_Server
will compute the default column value, nextval('tblserver_id_server_seq'),
which is 1.  Presto, collision.  Subsequent tries should work though.

If you want to insert things with specified ID_Server values, it's up
to you to advance the sequence counter past those values (use setval()).
In this particular example, though, I don't see why you don't just leave
off the ID_Server value from the INSERT and let it assign 1 from the
sequence.

            regards, tom lane