Thread: Strange behaviour updating primary key column.

[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

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