Re: renumber table

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(dot)spadea(at)gmail(dot)com
> <mailto:david(dot)spadea(at)gmail(dot)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(at)netwolves(dot)com
> <mailto:sclark(at)netwolves(dot)com>> wrote:
>
> Scott Marlowe wrote:
>
> On Wed, Jun 18, 2008 at 3:50 PM, Steve Clark
> <sclark(at)netwolves(dot)com <mailto:sclark(at)netwolves(dot)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(at)postgresql(dot)org <mailto:pgsql-general(at)postgresql(dot)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