I have been playing with POSTGRESQL and Java a bit and ran into the following problem, and I asked the following on the postgresql mailing list:
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.
I have been adding data with “COPY” into this table….does that break something?
Of course someone very quickly pointed me to the problem:
I used the “COPY … FROM” command to populate the tables, “manually” inserting values into the TABLES.
This breaks the automatic point in the “SERIAL”, which then no longer functions.
The trick was to leave the SERIAL out of the file so POSTGRESQL populated the SERIAL itself, and thereby keeping the pointer intact:
So the copy command will look something like this:
COPY reference (reference_text,reference_type,topic_id) FROM ‘/zfsdata/postgreshome/ref2.txt’;