X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id C428CD1C914 for ; Mon, 24 Nov 2003 16:31:24 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 61174-04 for ; Mon, 24 Nov 2003 12:30:52 -0400 (AST) Received: from mail-relay.rwa-net.co.uk (mail-relay.rwa-net.co.uk [62.189.139.18]) by svr1.postgresql.org (Postfix) with ESMTP id F0240D1D86A for ; Mon, 24 Nov 2003 12:30:50 -0400 (AST) Received: from saturn.rwa-net.co.uk (Saturn.rwa-net.co.uk [62.189.139.33]) by mail-relay.rwa-net.co.uk (8.11.6/8.11.6) with ESMTP id hAOGe5m01683 for ; Mon, 24 Nov 2003 16:40:05 GMT Received: from [62.189.139.142] by saturn.cvw.org.uk (NTMail 7.00.0018/NT4698.00.83343e8d) with ESMTP id qenfbaaa for pgsql-sql@postgresql.org; Mon, 24 Nov 2003 16:31:15 +0000 Message-ID: <008001c3b2a8$614bbff0$8e8bbd3e@rwanet.co.uk> From: "Matthew Lunnon" To: , References: <20031123205914.45e19925.gry@ll.mit.edu><001a01c3b26c$633e1150$8e8bbd3e@rwanet.co.uk> <20031124101233.454030d8.gry@ll.mit.edu> Subject: Re: increment int value in subset of rows? Date: Mon, 24 Nov 2003 16:31:12 -0000 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_007D_01C3B2A8.5F92C550" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4922.1500 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4925.2800 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.4 tagged_above=0.0 required=5.0 tests=ASCII_FORM_ENTRY, BAYES_30, HTML_20_30, REFERENCES X-Spam-Level: * X-Archive-Number: 200311/278 X-Sequence-Number: 16093 This is a multi-part message in MIME format. ------=_NextPart_000_007D_01C3B2A8.5F92C550 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Ok so what about using a constraint to enforce you uniqueness and then eith= er 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 woul= d be since I guess that adding the constraint may take some time! Your goin= g to have some issues with time since the index will need updating for ever= y key and this will not be O(0). Another other option is to use a generate= d id as the primary key and then you won't need your unique index on the co= mposite primary key. Matthew ----- Original Message -----=20 From: george young=20 To: pgsql-sql@postgresql.org=20 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" threw this fish to the penguins: > You could write a function to do it. >=20 > 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 -----=20 > From: george young=20 > To: pgsql-sql@postgresql.org=20 > Sent: Monday, November 24, 2003 1:59 AM > Subject: [SQL] increment int value in subset of rows? >=20 >=20 > [postgresql 7.4, SuSE x86 linux] > I have a table "rtest" with primary key (run,seq) and other data. Fo= r 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=3D'foo', seq=3D2, adjustin= g the seq up for > all subsequent foo rows. My first thought > was just: > update rtest set seq=3Dseq+1 where run=3D'foo' and seq>1; > which gets: > ERROR: Cannot insert a duplicate key into unique index rtest_pkey > no surprise :-(. >=20 > This doesn't work, since the *order* of execution of these updates > is not guaranteed, and I actually would need to start with the highes= t=20 > 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.=20=20 > How can I increment all the seq values for foo columns where seq > so= mething? >=20 > 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); >=20 > I want to shift all foo rows and insert a new one so that: > select * from rtest where run=3D'foo' order by seq;=20 > would get: >=20 > run | seq | data > -----+-----+------ > foo | 1 | 11 > foo | 2 | 999 > foo | 3 | 22 > foo | 4 | 33 > foo | 5 | 44 --=20 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@postgresql.org _____________________________________________________________________ This e-mail has been scanned for viruses by MCI's Internet Managed Scanni= ng Services - powered by MessageLabs. For further information visit http://= www.mci.com ------=_NextPart_000_007D_01C3B2A8.5F92C550 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Ok so what about using a constraint to enf= orce you=20 uniqueness and then either inside a transaction or after locking the table = drop=20 the constraint, do the update and then re-add the constraint. I am not sure= how=20 fast this would be since I guess that adding the constraint may take some= =20 time! Your going to have some issues with time since the index will ne= ed=20 updating for every key and this will not be O(0).  Another other optio= n is=20 to use a generated id as the primary key and then you won't need your uniqu= e=20 index on the composite primary key.
 
Matthew
 
----- Original Message -----
Fro= m:=20 george young=
To: pgsql-sql@postgresql.org
Sent: Monday, November 24, 2003 3:= 12=20 PM
Subject: Re: [SQL] increment int v= alue in=20 subset of rows?

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

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

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

>  = ;=20 ----- Original Message -----
>   From: george young=20
>   To: pgsql-sql@postgresql.org=20
>   Sent: Monday, November 24, 2003 1:59=20 AM
>   Subject: [SQL] increment int value in subset of=20 rows?
>
>
>   [postgresql 7.4, SuSE x86=20 linux]
>   I have a table "rtest" with primary key (run,s= eq)=20 and other data.  For a given value
>   of "run", seq= is a=20 sequential run of integers, 1,2,3,4..  Now I want to
> &n= bsp;=20 insert a row into this "sequence", say run=3D'foo', seq=3D2, adjusting th= e seq up=20 for
>   all subsequent foo rows.  My first=20 thought
>   was just:
>    &nbs= p;=20 update rtest set seq=3Dseq+1 where run=3D'foo' and seq>1;
> = ; =20 which gets:
>      ERROR:  Cannot ins= ert a=20 duplicate key into unique index rtest_pkey
>   no surpris= e=20 :-(.
>
>   This doesn't work, since the *order* of= =20 execution of these updates
>   is not guaranteed, and I= =20 actually would need to start with the highest
>   value = of=20 seq and work down.  There may be a thousand or so rows for=20 'foo'
>   run, so an external loop of queries would be ve= ry=20 expensive. 
>   How can I increment all the seq val= ues=20 for foo columns where seq > something?
>
>   cr= eate=20 table rtest(run text,seq int,data int,primary key=20 (run,seq));
>   insert into rtest=20 values('foo',1,11);
>   insert into rtest=20 values('foo',2,22);
>   insert into rtest=20 values('foo',3,33);
>   insert into rtest=20 values('foo',4,44);
>   insert into rtest=20 values('bar',1,99);
>
>   I want to shift all foo = rows=20 and insert a new one so that:
>      selec= t *=20 from rtest where run=3D'foo' order by seq;
>   would=20 get:
>
>    run | seq | data
> &nb= sp;=20 -----+-----+------
>    foo |   1 | &= nbsp;=20 11
>    foo |   2 | =20 999
>    foo |   3 |  =20 22
>    foo |   4 |  =20 33
>    foo |   5 |   44

= --=20
 I cannot think why the whole bed of the ocean is
 not o= ne=20 solid mass of oysters, so prolific they seem. Ah,
 I am wandering= !=20 Strange how the brain controls the brain!
-- Sherlock Holmes in "The D= ying=20 Detective"

---------------------------(end of=20 broadcast)---------------------------
TIP 1: subscribe and unsubscribe= =20 commands go to majordomo@postgresql.org
=
_____________________________________________________________________This=20 e-mail has been scanned for viruses by MCI's Internet Managed Scanning=20 Services - powered by MessageLabs. For further information visit http://www.mci.com
------=_NextPart_000_007D_01C3B2A8.5F92C550--