increment int value in subset of rows?
От | george young |
---|---|
Тема | increment int value in subset of rows? |
Дата | |
Msg-id | 20031123205914.45e19925.gry@ll.mit.edu обсуждение исходный текст |
Ответы |
Re: increment int value in subset of rows?
|
Список | pgsql-sql |
[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 | 11foo | 2 | 999foo | 3 | 22foo | 4 | 33foo | 5 | 44 -- I cannot think why the whole bed of the ocean isnot 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"
В списке pgsql-sql по дате отправления: