update vs unique index - Mailing list pgsql-sql

From jacekp@poczta.wprost.pl
Subject update vs unique index
Date
Msg-id 1122296323.723165.27560@o13g2000cwo.googlegroups.com
Whole thread Raw
Responses Re: update vs unique index
List 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 by date:

Previous
From: Bruno Wolff III
Date:
Subject: Re: int to date
Next
From: Alvaro Herrera
Date:
Subject: Re: update vs unique index