Search Postgresql Archives

Re: renumber table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx <mailto:david.spadea@xxxxxxxxx>> 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@xxxxxxxxxxxxx
    <mailto:sclark@xxxxxxxxxxxxx>> wrote:

        Scott Marlowe wrote:

            On Wed, Jun 18, 2008 at 3:50 PM, Steve Clark
            <sclark@xxxxxxxxxxxxx <mailto:sclark@xxxxxxxxxxxxx>> 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@xxxxxxxxxxxxxx <mailto:pgsql-general@xxxxxxxxxxxxxx>)
        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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux