Обсуждение: 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 | 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"
You could write a function to do it.
Matthew
----- Original Message -----From: george youngSent: Monday, November 24, 2003 1:59 AMSubject: [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
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 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"
Ok so what about using a constraint to enforce you uniqueness and then either inside a transaction or after locking the table drop the constraint, do the update and then re-add the constraint. I am not sure how fast this would be since I guess that adding the constraint may take some time! Your going to have some issues with time since the index will need updating for every key and this will not be O(0). Another other option is to use a generated id as the primary key and then you won't need your unique index on the composite primary key.
Matthew
----- Original Message -----From: george youngSent: Monday, November 24, 2003 3:12 PMSubject: Re: [SQL] increment int value in subset of rows?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"
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
_____________________________________________________________________
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
here is a work-a-round: # create table t (a int, primary key (a)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 't_pkey' for table 't' CREATE # insert into t values (1); # insert into t values (1); ERROR: Cannot insert a duplicate key into unique index t_pkey # insert into t values (2); INSERT 5110301 1 # insert into t values (3); INSERT 5110302 1 # update t set a = a+1; ERROR: Cannot insert a duplicate key into unique index t_pkey # update t set a = -a; UPDATE 3 # update t set a = -a + 1; UPDATE 3 # select * from t;a ---234 (3 rows) if i remember correctly, sql for smarties book has an item on this. On Sun, 23 Nov 2003, george young wrote: > 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?