Re: increment int value in subset of rows?

On Mon, 24 Nov 2003 09:21:39 -0000
"Matthew Lunnon" <mlunnon@rwa-net.co.uk> threw this fish to the penguins:

> You could write a function to do it.
> 
> Matthew

That would save me the external interaction, but still amount to ~1000
sql queries -- I'm hoping to find something O(0), i.e. a few queries
regardless of the number of rows...

>   ----- Original Message ----- 
>   From: george young 
>   To: pgsql-sql@postgresql.org 
>   Sent: Monday, November 24, 2003 1:59 AM
>   Subject: [SQL] increment int value in subset of rows?
> 
> 
>   [postgresql 7.4, SuSE x86 linux]
>   I have a table "rtest" with primary key (run,seq) and other data.  For a given value
>   of "run", seq is a sequential run of integers, 1,2,3,4..  Now I want to
>   insert a row into this "sequence", say run='foo', seq=2, adjusting the seq up for
>   all subsequent foo rows.  My first thought
>   was just:
>      update rtest set seq=seq+1 where run='foo' and seq>1;
>   which gets:
>      ERROR:  Cannot insert a duplicate key into unique index rtest_pkey
>   no surprise :-(.
> 
>   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?
> 
>   create table rtest(run text,seq int,data int,primary key (run,seq));
>   insert into rtest values('foo',1,11);
>   insert into rtest values('foo',2,22);
>   insert into rtest values('foo',3,33);
>   insert into rtest values('foo',4,44);
>   insert into rtest values('bar',1,99);
> 
>   I want to shift all foo rows and insert a new one so that:
>      select * from rtest where run='foo' order by seq; 
>   would get:
> 
>    run | seq | data
>   -----+-----+------
>    foo |   1 |   11
>    foo |   2 |  999
>    foo |   3 |   22
>    foo |   4 |   33
>    foo |   5 |   44

-- I cannot think why the whole bed of the ocean isnot one solid mass of oysters, so prolific they seem. Ah,I am
wandering!Strange how the brain controls the brain!-- Sherlock Holmes in "The Dying Detective"