update vs unique index
От | jacekp@poczta.wprost.pl |
---|---|
Тема | update vs unique index |
Дата | |
Msg-id | 1122296323.723165.27560@o13g2000cwo.googlegroups.com обсуждение исходный текст |
Ответы |
Re: update vs unique index
|
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: