From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Timo" <siroco(at)suomi24(dot)fi> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Discarding UNIQUE temporarily? |
Date: | 2004-01-19 07:50:44 |
Message-ID: | 21862.1074498644@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
"Timo" <siroco(at)suomi24(dot)fi> writes:
> create temp table foo (name text, rank int unique);
> ...
> update foo set rank = rank + 1 where rank > 2;
> But this, of course, won't do because the first update violates unique
> constraint which in my application is mandatory.
This should work according to the SQL spec, because UNIQUE constraints
are supposed to be tested as of the completion of a query. Postgres
currently does uniqueness checking incrementally, which can fail as
you've observed.
> Is there any simple workaround for this apart from writing a function?
Not really :-(. If you can identify a range of values that aren't
normally used in the table, you can do a horrid two-step kluge. For
example, if there aren't normally any negative ranks:
update foo set rank = -(rank + 1) where rank > 2;
update foo set rank = -rank where rank < 0;
> Wouldn't this kind of update-extension be handy:
> update foo set rank = rank + 1 where rank > 2 order by rank desc;
No. The correct fix is to make it behave per spec; there won't be a
lot of interest in introducing nonstandard language extensions to work
around the shortcoming ...
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | glenn | 2004-01-19 14:02:11 | Re: Meta data about object in postgres |
Previous Message | Bruno Wolff III | 2004-01-19 05:20:13 | Re: query |