Unique index hassles

Hi all,

I have the following table:

CREATE TABLE question
(
    qid             INTEGER         DEFAULT nextval('qid_seq'::text),
    pid             INTEGER         NOT NULL,
    order_val       SMALLINT        NOT NULL,
    qtypeid         SMALLINT        NOT NULL,
    label           VARCHAR(255)    NOT NULL,
    help_text       VARCHAR(255),

    PRIMARY KEY (qid),
    FOREIGN KEY (pid) REFERENCES page (pid),
    FOREIGN KEY (qtypeid) REFERENCES qtype (qtypeid)
);
CREATE INDEX idx_question1 ON question (pid,order_val);

This index used to be unique, but it caused the following problem, so I
had to make it non-unique.

The data in it looks like:

 qid | pid | order_val | qtypeid |      label       | help_text
-----+-----+-----------+---------+------------------+-----------
   9 |   1 |         1 |       1 | hello            |
   8 |   1 |         4 |       1 | Checkbox anyone? |
  20 |   1 |         2 |      10 | radio man        |
  18 |   1 |         3 |       1 | hello again      |

When I issue the following query

UPDATE question SET order_val = order_val + 1 WHERE order_val > 1;

I get the following error:

ERROR:  Cannot insert a duplicate key into unique index idx_question1

I know why this is happening (it needs to increment the values in
decreasing order of order_val), and I think it stinks. However, I'm
perfectly willing to believe it's my fault not that of Postgres.

Is there anyway to have the query above succeed? Do I need to do it in a
transaction? Perhaps psql has autocommit on?

I'd really, really appreciate any pointers.

TIA
Rich


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----==  Over 80,000 Newsgroups - 16 Different Servers! =-----