Re: renumber table - Mailing list pgsql-general

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