From: george young <gry@ll.mit.edu>
To: pgsql-sql@postgresql.org
Subject: Re: increment int value in subset of rows?
Date: Mon, 24 Nov 2003 10:12:33 -0500
Message-ID: <20031124101233.454030d8.gry@ll.mit.edu> (raw)
In-Reply-To: <001a01c3b26c$633e1150$8e8bbd3e@rwanet.co.uk>
References: <20031123205914.45e19925.gry@ll.mit.edu>
<001a01c3b26c$633e1150$8e8bbd3e@rwanet.co.uk>
On Mon, 24 Nov 2003 09:21:39 -0000
"Matthew Lunnon" <mlunnon@rwa-net.co.uk> threw this fish to the penguins:
> You could write a function to do it.
>
> Matthew
That would save me the external interaction, but still amount to ~1000
sql queries -- I'm hoping to find something O(0), i.e. a few queries
regardless of the number of rows...
> ----- 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"
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: gry@ll.mit.edu
Subject: Re: increment int value in subset of rows?
In-Reply-To: <20031124101233.454030d8.gry@ll.mit.edu>
* 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