Re: renumber table

On Wed, Jun 18, 2008 at 2:58 PM, Steve Clark <sclark(at)netwolves(dot)com> wrote:
> Hello List,
>
> I have acquired the task of maintaining and existing application that uses
> postgresql. I am only lightly versed
> in sql and have the following problem I need to solve.
>
> I have a table in which each row has a column - row_number. The row_numbers
> need to be sequential.

I question this. Someone may have arbitrarily decided it needs to be
sequential, but it's quite likely that your business process does NOT
need it to be sequential, at least not in the database.

Any solution that involves actually renumbering the rows is a
guaranteed performance loser, and a good way to scramble your data in
wonderful, horrible, scary new ways.

It's generally better to use a separate table with a numbering schema
in it that points to the rows so you don't have to actually update the
rows themselves, only the numbering scheme. But even that is fraught
with horrible possibilities in terms of social engineering problems
that show up.

Update number 154929! I was working on it last month and it needs x
added to it!

Someone updates 154929 only to find out that they should have updated
154924 instead because of the renumbering.