From: Matthew Lunnon <mlunnon@rwa-net.co.uk>
To: gry@ll.mit.edu
To: pgsql-sql@postgresql.org
Subject: Re: increment int value in subset of rows?
Date: Mon, 24 Nov 2003 09:21:39 -0000
Message-ID: <001a01c3b26c$633e1150$8e8bbd3e@rwanet.co.uk> (raw)
References: <20031123205914.45e19925.gry@ll.mit.edu>
You could write a function to do it.
Matthew
----- 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 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 Scanning Services - powered by MessageLabs. For further information visit http://www.mci.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mlunnon@rwa-net.co.uk, gry@ll.mit.edu
Subject: Re: increment int value in subset of rows?
In-Reply-To: <001a01c3b26c$633e1150$8e8bbd3e@rwanet.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox