Re: Unique index hassles

On Wed, 21 May 2003 20:47:49 +0100, "Richard Gration"
<richard@zync.co.uk> wrote:
>CREATE TABLE question
>(   [...]
>    pid             INTEGER         NOT NULL,
>    order_val       SMALLINT        NOT NULL,
>);
>CREATE [UNIQUE] INDEX idx_question1 ON question (pid,order_val);
>
>UPDATE question SET order_val = order_val + 1 WHERE order_val > 1;
>ERROR:  Cannot insert a duplicate key into unique index idx_question1

Can't comment on whether this is a bug, a flaw, or intended behaviour.
If you have an *implicit* constraint order_val > 0, then a workaround
would be

    BEGIN;
    UPDATE question SET order_val = - order_val
     WHERE order_val > 1 [AND hopefully pid = something];
    UPDATE question SET order_val = - order_val + 1
     WHERE order_val < 0 [AND ...];
    COMMIT;

Servus
 Manfred