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 --- 2 3 4 (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? ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
