Consider such table:
CREATE TABLE test (idx integer);
populated by following statements:
INSERT INTO test VALUES (1);
INSERT INTO test VALUES (2);
INSERT INTO test VALUES (3);
since idx schould be unique, we need an index
CREATE UNIQUE INDEX i_test ON test(idx);
Following SQL command fails:
UPDATE test SET idx = idx + 1;
I can imagine why it fails. Update operates on first row, making 2 out
of 1 and that collides with second row (which has 2 as its value
already). However, when you look at the update efect as a whole
uniqueness is preserved, so index schould not veto update.
My question is: is there a chance to bypass this behaviour? Something
like controlling the order in which rows go into update. If update
would start from last row, it would be successful for sure.
regards,
--
Jacek Prucia