From: Richard Huxton <dev@archonet.com>
To: Luis Neves <lneves@netcabo.pt>
Cc: pgsql-sql@postgresql.org
Subject: Re: Strange behaviour updating primary key column.
Date: Mon, 21 Jun 2004 11:14:59 +0100
Message-ID: <40D6B523.9040109@archonet.com> (raw)
In-Reply-To: <200406210729.07185.lneves@netcabo.pt>
References: <200406210729.07185.lneves@netcabo.pt>
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
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, lneves@netcabo.pt
Subject: Re: Strange behaviour updating primary key column.
In-Reply-To: <40D6B523.9040109@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