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
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
