Re: Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug? - Mailing list pgsql-general

From Reece Hart
Subject Re: Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?
Date
Msg-id 1190671101.6148.71.camel@snafu
Whole thread Raw
In response to Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?  ("Anoo Sivadasan Pillai" <aspillai@in.rm.com>)
List pgsql-general
On Mon, 2007-09-24 at 12:50 +0530, Anoo Sivadasan Pillai wrote:
CREATE TABLE master ( m1 INT primary key , m2 VARCHAR(100)) ;
INSERT  INTO master VALUES  ( 1, 'm1' ) ;
INSERT  INTO master VALUES  ( 2, 'm2' ) ;
UPDATE  master SET m1 = m1 + 1;
Update fails with the message - ERROR: duplicate key violates unique constraint "master_pkey"

Primary key constraints are not deferred and are not deferrable (try: \x, then select * from pg_constraint where conname~'master_pkey'). This means that the constraint is checked immediately for each row updated. Success will depend on the order in which postgresql decides to visit rows: if it visits all rows in descending order, you'll be fine, but that's unlikely and uncontrollable (AFAIK).

Here's a sketch of an easy workaround. You might have to modify it for your particular range of m1.
begin;
update master set m1=-m1;
update master set m1=-m1+1;
commit;

You could just as easily add N to m1, then subtract (N-1) from m1. You'll need N>max(m1).

Good luck,
Reece

-- 
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

pgsql-general by date:

Previous
From: "Charles.Hou"
Date:
Subject: about the PostgreSql loading?
Next
From: "Pavel Stehule"
Date:
Subject: Re: set returning functions.