You could write a function to do it.
Matthew
----- Original Message -----
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
|