Re: problems with unique key on table with serial

chris Gnther <guenther(at)uscreen(dot)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