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 A14ADD1D8D2 for ; Mon, 24 Nov 2003 09:21:36 +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 17424-09 for ; Mon, 24 Nov 2003 05:21:06 -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 781CDD1B531 for ; Mon, 24 Nov 2003 05:21:03 -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 hAO9UWm00364 for ; Mon, 24 Nov 2003 09:30:33 GMT Received: from [62.189.139.142] by saturn.cvw.org.uk (NTMail 7.00.0018/NT4698.00.83343e8d) with ESMTP id uemfbaaa for pgsql-sql@postgresql.org; Mon, 24 Nov 2003 09:21:48 +0000 Message-ID: <001a01c3b26c$633e1150$8e8bbd3e@rwanet.co.uk> From: "Matthew Lunnon" To: , References: <20031123205914.45e19925.gry@ll.mit.edu> Subject: Re: increment int value in subset of rows? Date: Mon, 24 Nov 2003 09:21:39 -0000 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0011_01C3B26C.5D6E55F0" 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/272 X-Sequence-Number: 16087 This is a multi-part message in MIME format. ------=_NextPart_000_0011_01C3B26C.5D6E55F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable You could write a function to do it. Matthew ----- 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? [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=3D'foo', seq=3D2, adjusting th= e 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 :-(. 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=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 > someth= ing? 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=3D'foo' order by seq;=20 would get: 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html _____________________________________________________________________ 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_0011_01C3B26C.5D6E55F0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
You could write a function to do it.
 
Matthew
 
----- Original Message -----
Fro= m:=20 george young=
To: pgsql-sql@postgresql.org
Sent: Monday, November 24, 2003 1:= 59=20 AM
Subject: [SQL] increment int value= in=20 subset of rows?

[postgresql 7.4, SuSE x86 linux]
I have a table "rtest"= with=20 primary key (run,seq) and other data.  For a given value
of "run"= , seq=20 is a sequential run of integers, 1,2,3,4..  Now I want to
insert = a row=20 into this "sequence", say run=3D'foo', seq=3D2, adjusting the seq up for<= BR>all=20 subsequent foo rows.  My first thought
was just:
  = =20 update rtest set seq=3Dseq+1 where run=3D'foo' and seq>1;
which=20 gets:
   ERROR:  Cannot insert a duplicate key into uni= que=20 index rtest_pkey
no surprise :-(.

This doesn't work, since the= =20 *order* of execution of these updates
is not guaranteed, and I actuall= y=20 would need to start with the highest
value of seq and work down. = ;=20 There may be a thousand or so rows for 'foo'
run, so an external loop = of=20 queries would be very expensive. 
How can I increment all the se= q=20 values for foo columns where seq > something?

create table rtes= t(run=20 text,seq int,data int,primary key (run,seq));
insert into rtest=20 values('foo',1,11);
insert into rtest values('foo',2,22);
insert in= to=20 rtest values('foo',3,33);
insert into rtest values('foo',4,44);
ins= ert=20 into rtest values('bar',1,99);

I want to shift all foo rows and in= sert=20 a new one so that:
   select * from rtest where run=3D'foo' = order=20 by seq;
would get:

 run | seq |=20 data
-----+-----+------
 foo |   1 |  =20 11
 foo |   2 |  999
 foo |   3= =20 |   22
 foo |   4 |   33
 f= oo=20 |   5 |   44

--
 I cannot think why t= he=20 whole bed of the ocean is
 not one solid mass of oysters, so prol= ific=20 they seem. Ah,
 I am wandering! Strange how the brain controls th= e=20 brain!
-- Sherlock Holmes in "The Dying=20 Detective"

---------------------------(end of=20 broadcast)---------------------------
TIP 5: Have you checked our exte= nsive=20 FAQ?

          &= nbsp;   =20 http://www.postgres= ql.org/docs/faqs/FAQ.html

______________________________________= _______________________________
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_0011_01C3B26C.5D6E55F0--