Thread: 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/

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