From: Richard Huxton <dev@archonet.com>
To: remco lengers <pgsql@lengers.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: ERROR: duplicate key violates unique constraint
Date: Tue, 20 Mar 2007 09:27:42 +0000
Message-ID: <45FFA90E.1020405@archonet.com> (raw)
In-Reply-To: <32207abd0703200211l46acdab7p5a2d68d69ffc5b6c@mail.gmail.com>
References: <32207abd0703200211l46acdab7p5a2d68d69ffc5b6c@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, pgsql@lengers.com
Subject: Re: ERROR: duplicate key violates unique constraint
In-Reply-To: <45FFA90E.1020405@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox