Re: ERROR: duplicate key violates unique constraint

remco lengers wrote:
> Hi List,
> 
> Its been a while since I worked with databases and I am running into the
> following which I have not been able to find the root cause for:
> 
> I have the follow SQL statement:
> 
> INSERT INTO reference VALUES (DEFAULT,'123','2',1);
> 
> Which generates the following error:
> 
> "ERROR:  duplicate key violates unique constraint "reference_pkey""
> 
> And the table definition looks like:
> 
> CREATE TABLE reference (
> referencelist_nr serial unique,
> reference_text varchar(40) NOT NULL,
> reference_type integer NOT NULL,
> Topic_Id integer NOT NULL,
> PRIMARY KEY (referencelist_nr),
> FOREIGN KEY (Topic_Id) REFERENCES Topic (Topic_Id),
> FOREIGN KEY (reference_type) REFERENCES reference_type (reference_type_nr)
> );
> 
> It seems to me for some reason "DEFAULT" doesn't select the next SERIAL.

It does, but if you've been manually setting that value then the SERIAL 
doesn't know about it. Sequences are fast and light, but they can be so 
because they don't have to check the table.

> I have been adding data with "COPY" into this table....does that break
> something?

There you go. After a bulk load you'll want to run something like:
SELECT setval('reference_referencelist_nr_seq', (SELECT 
max(referencelist_nr)+1 FROM reference));
Have a look at pg_dump to see how it does this sort of thing.

--   Richard Huxton  Archonet Ltd