Re: increment int value in subset of rows?

On Mon, 24 Nov 2003 09:21:39 -0000
"Matthew Lunnon" <mlunnon(at)rwa-net(dot)co(dot)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(at)postgresql(dot)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 is
not 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"