Re: renumber table

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>
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> wrote:
>
>> Scott Marlowe wrote:
>>
>>> On Wed, Jun 18, 2008 at 3:50 PM, Steve Clark <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)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>