Skip site navigation (1) Skip section navigation (2)

Re: BUG #2393: update fails with unique constraint violation

From: "Harald Armin Massa" <haraldarminmassa(at)gmail(dot)com>
To: "Laurence Dawson" <larry(dot)dawson(at)vanderbilt(dot)edu>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2393: update fails with unique constraint violation
Date: 2006-04-15 13:54:01
Message-ID: 7be3f35d0604150654wd6b3c10i5a6a4eb863404306@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Laurence,

that error is correct:

> CREATE TABLE test.test
> (
>   a int4 NOT NULL DEFAULT nextval('test.test_a_seq'::regclass),
>   CONSTRAINT pk PRIMARY KEY (a)
> )
> WITHOUT OIDS;
> And then try an update:
> lstore=> select * from test.test;
> a
> ----
>   1
>   2
>   3
>   4
>   5
>   6
>   7
>   8
>   9
> 10
>
> lstore=> update test.test set a = a + 2 where a >= 3;
> ERROR:  duplicate key violates unique constraint "pk"
>

it starts anywhere in the table and updates line by line. So if it starts
with, say, a=4, it trys to set a=4+2, giving 6 which is allready present.

possible solution: create a temp table from a select with that a+x, and then
freshen your data from there.

On a side node ... if you have to change your PRIMARY KEY in this fashion,
there is propably a design error within your database scheme / application.

Best wishes

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstra├če 202b
70197 Stuttgart
0173/9409607
-
PostgreSQL - supported by a community that does not put you on hold

In response to

pgsql-bugs by date

Next:From: T.J. FerraroDate: 2006-04-15 13:56:13
Subject: Re: BUG #2393: update fails with unique constraint violation
Previous:From: Peter BrantDate: 2006-04-14 19:48:06
Subject: Re: Permission denied on fsync / Win32 (was right

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group