Re: increment int value in subset of rows?

Ok so what about using a constraint to enforce you uniqueness and then either inside a transaction or after locking the table drop the constraint, do the update and then re-add the constraint. I am not sure how fast this would be since I guess that adding the constraint may take some time! Your going to have some issues with time since the index will need updating for every key and this will not be O(0). Another other option is to use a generated id as the primary key and then you won't need your unique index on the composite primary key.

Matthew

----- Original Message -----
From: george young
To: pgsql-sql(at)postgresql(dot)org
Sent: Monday, November 24, 2003 3:12 PM
Subject: Re: [SQL] 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"

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

_____________________________________________________________________
This e-mail has been scanned for viruses by MCI's Internet Managed Scanning Services - powered by MessageLabs. For further information visit http://www.mci.com