From: | jacekp(at)poczta(dot)wprost(dot)pl |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | update vs unique index |
Date: | 2005-07-25 12:58:43 |
Message-ID: | 1122296323.723165.27560@o13g2000cwo.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruno Wolff III | 2005-07-25 16:14:35 | Re: int to date |
Previous Message | Daniel Drotos | 2005-07-25 08:54:42 | int to date |