here is a work-a-round:
# create table t (a int, primary key (a));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 't_pkey' for table 't'
CREATE
# insert into t values (1);
# insert into t values (1);
ERROR: Cannot insert a duplicate key into unique index t_pkey
# insert into t values (2);
INSERT 5110301 1
# insert into t values (3);
INSERT 5110302 1
# update t set a = a+1;
ERROR: Cannot insert a duplicate key into unique index t_pkey
# update t set a = -a;
UPDATE 3
# update t set a = -a + 1;
UPDATE 3
# select * from t;a
---234
(3 rows)
if i remember correctly, sql for smarties book has an item on this.
On Sun, 23 Nov 2003, george young wrote:
> This doesn't work, since the *order* of execution of these updates
> is not guaranteed, and I actually would need to start with the highest
> value of seq and work down. There may be a thousand or so rows for 'foo'
> run, so an external loop of queries would be very expensive.
> How can I increment all the seq values for foo columns where seq > something?