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! =-----