Обсуждение: ERROR: duplicate key violates unique constraint
Hi List,<br /><br />Its been a while since I worked with databases and I am running into the following which I have not beenable to find the root cause for:<br /><br />I have the follow SQL statement:<br /><br />INSERT INTO reference VALUES(DEFAULT,'123','2',1); <br /><br />Which generates the following error: <br /><br />"ERROR: duplicate key violatesunique constraint "reference_pkey""<br /><br />And the table definition looks like:<br /><br />CREATE TABLE reference(<br /> referencelist_nr serial unique, <br />reference_text varchar(40) NOT NULL, <br />reference_type integerNOT NULL, <br />Topic_Id integer NOT NULL,<br />PRIMARY KEY (referencelist_nr),<br />FOREIGN KEY (Topic_Id) REFERENCESTopic (Topic_Id),<br />FOREIGN KEY (reference_type) REFERENCES reference_type (reference_type_nr) <br />);<br /><br/>It seems to me for some reason "DEFAULT" doesn't select the next SERIAL.<br />If I run: <br /><br />INSERT INTO referenceVALUES (14,'123','2',1);<br /><br />14 being the next free integer it works fine.....<br /><br />I have been addingdata with "COPY" into this table....does that break something?<br /><br />Your help/tips/insights are appreciated.<br/><br />Solaris 10/Postgresql 8.1.4<br /><br />Regards,<br /><span><br />..Remco</span>
unsubscribe _________________________________________________________________ FREE pop-up blocking with the new MSN Toolbar - get it now! http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/
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@lengers.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
>>> "remco lengers" <pgsql@lengers.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
unsubscribe ionitastefan@hotmail.com _________________________________________________________________ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
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
Stefan Ionita wrote: > unsubscribe Marc, I would think that these list don't have the "administrativia" flag set in the configuration. This is in the "moderation" class. The help for this option is: If the administrivia setting is set to "yes", posted messages which match the patterns in the admin_body and admin_headerssettings will be sent to the moderators for review. These two settings are used to keep commands, such as "remove"or "unsubscribe", from being posted to a mailing list. If the administrivia setting is set to "no", the admin_body and admin_headers settings will have no effect. The pgsql-es-ayuda list had it set to "no" (I just changed it), and it said This value was determined by the DEFAULT settings. So maybe the other lists are missing this setting. I remember Bruce complaining about this not long ago (and threatening with Mailman!?) -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support