Re: renumber table

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
>