Re: ERROR: duplicate key violates unique constraint

Use
INSERT INTO reference(reference_text, reference_type, Topic_Id) VALUES (DEFAULT,'123','2',1);
instead.
Since referencelist_nr is a serial PostgreSQL will provide the next free integer itself.

>>> "remco lengers" <pgsql(at)lengers(dot)com> 2007-03-20 10:11 >>>
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.
If I run:

INSERT INTO reference VALUES (14,'123','2',1);

14 being the next free integer it works fine.....

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

Your help/tips/insights are appreciated.

Solaris 10/Postgresql 8.1.4

Regards,

..Remco