pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedStrange behaviour updating primary key column.
2+ messages / 2 participants
[nested] [flat]
* Strange behaviour updating primary key column.
@ 2004-06-21 06:29 Luis Neves <lneves@netcabo.pt>
2004-06-21 10:14 ` Re: Strange behaviour updating primary key column. Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 2+ messages in thread
From: Luis Neves @ 2004-06-21 06:29 UTC (permalink / raw)
To: pgsql-sql
[Sorry if this is a double post.]
Hello all,
[Postgresql 7.4.2]
CREATE TABLE "story" (
"id" int4 NOT NULL,
"title" varchar (100) NOT NULL,
"body" text NOT NULL,
"entryDate" timestamptz NOT NULL,
CONSTRAINT "story_pkey" PRIMARY KEY (id)
);
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.
Can anyone with a better understanding of PostgreSQL explain to me why this
happens?
Are my expectations wrong?
Best regards,
Luis Neves
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Strange behaviour updating primary key column.
2004-06-21 06:29 Strange behaviour updating primary key column. Luis Neves <lneves@netcabo.pt>
@ 2004-06-21 10:14 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 2+ messages in thread
From: Richard Huxton @ 2004-06-21 10:14 UTC (permalink / raw)
To: Luis Neves <lneves@netcabo.pt>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-06-21 10:14 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-21 06:29 Strange behaviour updating primary key column. Luis Neves <lneves@netcabo.pt>
2004-06-21 10:14 ` Richard Huxton <dev@archonet.com>
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