From: jacekp@poczta.wprost.pl
To: pgsql-sql@postgresql.org
Subject: update vs unique index
Date: 25 Jul 2005 05:58:43 -0700
Message-ID: <1122296323.723165.27560@o13g2000cwo.googlegroups.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jacekp@poczta.wprost.pl
Subject: Re: update vs unique index
In-Reply-To: <1122296323.723165.27560@o13g2000cwo.googlegroups.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox