Message-ID: <40D6B523.9040109@archonet.com>
Date: Mon, 21 Jun 2004 11:14:59 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 0.7 (X11/20040615)
MIME-Version: 1.0
To: Luis Neves <lneves@netcabo.pt>
Cc: pgsql-sql@postgresql.org
Subject: Re: Strange behaviour updating primary key column.
References: <200406210729.07185.lneves@netcabo.pt>
In-Reply-To: <200406210729.07185.lneves@netcabo.pt>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Luis Neves wrote:
> In the above table why does the query:
> 
> UPDATE "story"
> SET id = (id + 1500);
> 
> fails with:
> "ERROR:  duplicate key violates unique constraint 'story_pkey'"
> 
> (I have 6000 records in the table with "id" spanning from 1 to 6000)
> 
> I find this behaviour strange, SQL is a set based language, but 
> PostgreSQL is acting like is changing the values in sequence.

Quite right - it shouldn't happen. The issue is that the unique check 
isn't deferred, but is made whenever a value changes. So 1=>1501 of 
course gives the error.

The work-around usually given is to do two updates:
   UPDATE story SET id = -id;
   UPDATE story SET id = -id + 1500;

The real solution would be to check unique constraints at the end of 
statement, but I assume this is a tricky change or it would have been 
done by now.

-- 
   Richard Huxton
   Archonet Ltd