Обсуждение: renumber table

Hello List,

I have acquired the task of maintaining and existing application that uses postgresql. I am only lightly versed
in sql and have the following problem I need to solve.

I have a table in which each row has a column - row_number. The row_numbers need to be sequential.
Everything is fine until a row in the middle of the table gets deleted, now I need to renumber the remaining
rows so they are sequential again. The table is small, typically less than a 100 rows. Is there a simple way
to do this in sql. I know I can dump the table to an external file and then use 'C' or a bash script to fix up the
row_numbers and then reload the table - but it seems there is probably a more elegant way to do this in sql.

Anyway thanks for any tips.

Steve

On Wed, Jun 18, 2008 at 2:58 PM, Steve Clark <sclark@netwolves.com> wrote:
> Hello List,
>
> I have acquired the task of maintaining and existing application that uses
> postgresql. I am only lightly versed
> in sql and have the following problem I need to solve.
>
> I have a table in which each row has a column - row_number. The row_numbers
> need to be sequential.

I question this.  Someone may have arbitrarily decided it needs to be
sequential, but it's quite likely that your business process does NOT
need it to be sequential, at least not in the database.

Any solution that involves actually renumbering the rows is a
guaranteed performance loser, and a good way to scramble your data in
wonderful, horrible, scary new ways.

It's generally better to use a separate table with a numbering schema
in it that points to the rows so you don't have to actually update the
rows themselves, only the numbering scheme.  But even that is fraught
with horrible possibilities in terms of social engineering problems
that show up.

Update number 154929!  I was working on it last month and it needs x
added to it!

Someone updates 154929 only to find out that they should have updated
154924 instead because of the renumbering.

Scott Marlowe wrote:
> On Wed, Jun 18, 2008 at 2:58 PM, Steve Clark <sclark@netwolves.com> wrote:
>
>>Hello List,
>>
>>I have acquired the task of maintaining and existing application that uses
>>postgresql. I am only lightly versed
>>in sql and have the following problem I need to solve.
>>
>>I have a table in which each row has a column - row_number. The row_numbers
>>need to be sequential.
>
>
> I question this.  Someone may have arbitrarily decided it needs to be
> sequential, but it's quite likely that your business process does NOT
> need it to be sequential, at least not in the database.
>
> Any solution that involves actually renumbering the rows is a
> guaranteed performance loser, and a good way to scramble your data in
> wonderful, horrible, scary new ways.
>
> It's generally better to use a separate table with a numbering schema
> in it that points to the rows so you don't have to actually update the
> rows themselves, only the numbering scheme.  But even that is fraught
> with horrible possibilities in terms of social engineering problems
> that show up.
>
> Update number 154929!  I was working on it last month and it needs x
> added to it!
>
> Someone updates 154929 only to find out that they should have updated
> 154924 instead because of the renumbering.
>
>
Thanks Scott,

I realize this is certainly not the best design - but at this point in time it can't be changed. The table
is rarely updated and never concurrently and is very small, typically less than 100 rows so there really is
no performance impact.

Steve

On Wed, Jun 18, 2008 at 3:50 PM, Steve Clark <sclark@netwolves.com> wrote:
>
> I realize this is certainly not the best design - but at this point in time
> it can't be changed. The table
> is rarely updated and never concurrently and is very small, typically less
> than 100 rows so there really is
> no performance impact.

Then the easiest way to renumber a table like that is to do something like:

create temp sequence myseq;
update table set idfield=nextval('myseq');

and hit enter.
and pray.  :)

Scott Marlowe wrote:
> On Wed, Jun 18, 2008 at 3:50 PM, Steve Clark <sclark@netwolves.com> wrote:
>
>>I realize this is certainly not the best design - but at this point in time
>>it can't be changed. The table
>>is rarely updated and never concurrently and is very small, typically less
>>than 100 rows so there really is
>>no performance impact.
>
>
> Then the easiest way to renumber a table like that is to do something like:
>
> create temp sequence myseq;
> update table set idfield=nextval('myseq');
>
> and hit enter.
> and pray.  :)
>
>
Hi Scott,

I am not sure that will do what I want. As an example
suppose I have 5 rows and the idfield is 1,2,3,4,5
now row 1 is updated, not the idfield but another column, then row 3 is deleted.
Now I would like to renumber  them such that 1 is 1, 2 is 2, 4 is 4 , 5 is 4.

I don't think what you wrote will necessarily keep them in the same relative order that they
were before.

Regards,
Steve

On Thu, Jun 19, 2008 at 7:54 AM, Steve Clark <sclark@netwolves.com> wrote:

> I am not sure that will do what I want. As an example
> suppose I have 5 rows and the idfield is 1,2,3,4,5
> now row 1 is updated, not the idfield but another column, then row 3 is
> deleted.
> Now I would like to renumber  them such that 1 is 1, 2 is 2, 4 is 4 , 5 is
> 4.
>
> I don't think what you wrote will necessarily keep them in the same relative
> order that they
> were before.

It's ugly and a hack, but if you could do:

1. rename the table
2. create a new copy of the table. give the idfield a sequence.
3. select all the records from the renamed table, ordering by idfield,
and insert all fields other than idfield into the new table.
4. drop the renamed table

You'll maintain ordering and have a nice sequential idfield.
--
- David T. Wilson
david.t.wilson@gmail.com

Steve,

Here's your problem and its solution as I understand it:

-- Given an example table like this (data isn't too important -- just the sequencing)
create table meh
(
      id        serial primary key
    , word   varchar(10)
);

-- Populate it with data

insert into meh (word) values
    ('one'),
    ('two'),
    ('three'),
    ('four'),
    ('five'),
    ('six'),
    ('seven'),
    ('eight'),
    ('nine'),
    ('ten');

-- Delete a row from the middle of the table
delete from meh where id = 5;

-- Renumber all of the rows ABOVE the deleted row
-- This will maintain sequencing. This assumes that no gaps existed prior to the delete of this row,
-- and that only one row was deleted.

update meh
set id = id - 1
where id > 5;

At this point, if you've got a sequence that populates the id field, you'll need to set its nextval.


Dave

Steve,

I'd just like to add that I agree with Scott that this is asking for trouble if the field being renumbered is used as a foreign key somewhere. If you have no way of changing this logic, you should at least look into 'on delete cascade' and 'on update cascade' on your dependent tables. You can expect performance to suffer if the dependent tables are large, but at least you don't lose referential integrity.

Dave

David Spadea wrote:
> Steve,
>
> I'd just like to add that I agree with Scott that this is asking for
> trouble if the field being renumbered is used as a foreign key
> somewhere. If you have no way of changing this logic, you should at
> least look into 'on delete cascade' and 'on update cascade' on your
> dependent tables. You can expect performance to suffer if the dependent
> tables are large, but at least you don't lose referential integrity.
>
> Dave
>
>
>
> On Thu, Jun 19, 2008 at 7:07 PM, David Spadea <david.spadea@gmail.com
> <mailto:david.spadea@gmail.com>> wrote:
>
>     Steve,
>
>     Here's your problem and its solution as I understand it:
>
>     -- Given an example table like this (data isn't too important --
>     just the sequencing)
>     create table meh
>     (
>           id        serial primary key
>         , word   varchar(10)
>     );
>
>     -- Populate it with data
>
>     insert into meh (word) values
>         ('one'),
>         ('two'),
>         ('three'),
>         ('four'),
>         ('five'),
>         ('six'),
>         ('seven'),
>         ('eight'),
>         ('nine'),
>         ('ten');
>
>     -- Delete a row from the middle of the table
>     delete from meh where id = 5;
>
>     -- Renumber all of the rows ABOVE the deleted row
>     -- This will maintain sequencing. This assumes that no gaps existed
>     prior to the delete of this row,
>     -- and that only one row was deleted.
>
>     update meh
>     set id = id - 1
>     where id > 5;
>
>     At this point, if you've got a sequence that populates the id field,
>     you'll need to set its nextval.
>
>
>     Dave
>
>
>     On Thu, Jun 19, 2008 at 7:54 AM, Steve Clark <sclark@netwolves.com
>     <mailto:sclark@netwolves.com>> wrote:
>
>         Scott Marlowe wrote:
>
>             On Wed, Jun 18, 2008 at 3:50 PM, Steve Clark
>             <sclark@netwolves.com <mailto:sclark@netwolves.com>> wrote:
>
>                 I realize this is certainly not the best design - but at
>                 this point in time
>                 it can't be changed. The table
>                 is rarely updated and never concurrently and is very
>                 small, typically less
>                 than 100 rows so there really is
>                 no performance impact.
>
>
>
>             Then the easiest way to renumber a table like that is to do
>             something like:
>
>             create temp sequence myseq;
>             update table set idfield=nextval('myseq');
>
>             and hit enter.
>             and pray.  :)
>
>
>         Hi Scott,
>
>         I am not sure that will do what I want. As an example
>         suppose I have 5 rows and the idfield is 1,2,3,4,5
>         now row 1 is updated, not the idfield but another column, then
>         row 3 is deleted.
>         Now I would like to renumber  them such that 1 is 1, 2 is 2, 4
>         is 4 , 5 is 4.
>
>         I don't think what you wrote will necessarily keep them in the
>         same relative order that they
>         were before.
>
>         Regards,
>         Steve
>
>
>         --
>         Sent via pgsql-general mailing list
>         (pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org>)
>         To make changes to your subscription:
>         http://www.postgresql.org/mailpref/pgsql-general
>
>
>
Thanks to all that replied.

I agree the design wasn't the best and we had a function similar to what
you describe to keep things in order. Problem was we had a foreign key constraint that caused a row
to be deleted, because the foreign key was deleted when it shouldn't have been. So now the table
row numbering was messed up. It really doesn't cause a problem but when the table information gets
displayed it uses the row num for access to the table and looked wierd with the gaps in the numbering.

I took the easy way out and before displaying the table I check to see if max(row_num) is not equal to
count(*) then I renumber it in the php script that displays it using a loop.

Thanks again.
Steve