Re: Numbering a records

On Wednesday 18 February 2004 12:56, NTPT wrote:
> I have this table
>
> content (id int8,owner int8,position int8,timestamp int8,description
> text,batch int8)
>
> Table is inserted/deleted frequently, 'id' is almoust random.
>
> I insert to the table following set of rows :
>
> 12345, 1000,1,timestamp,blabla,0
> 12349, 1000,2,timestamp,blabla,0
> 12355, 1001,1,timestamp,blabla,0
> 12389, 1000,3,timestamp,blabla,0
> etc.. There is a many of these records.

> But now, some records are inserted, some deleted, some have the timestamp
> column updated, so column 'position' is not sequential anymore.

If you really need to update "position" to be sequential the simplest method
is procedural code. That is, loop through the records updating "position"
either from client code or using plpgsql.

PS - there is a "timestamp with time zone" type you might want for column
"timestamp"

PPS - Do you really want position/batch to be int8? You're really going to
have several billion batches?

--
Richard Huxton
Archonet Ltd