Date: Sun, 23 Nov 2003 20:59:14 -0500
From: george young <gry@ll.mit.edu>
To: pgsql-sql@postgresql.org
Subject: increment int value in subset of rows?
Message-Id: <20031123205914.45e19925.gry@ll.mit.edu>
Reply-To: gry@ll.mit.edu
Organization: MIT Lincoln Laboratory
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

[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"