Thread: Numbering a records

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.

Now I need to od some select like this

select * from content where owner='1000' order by timestamp with some limits, offsets etc. It is OK, no problem.

Other select, like to need  select a record of user 1000  WHERE position >5 AND position <150  is OK,

But now, some records are inserted, some deleted, some have the timestamp column updated, so column 'position' is not
sequentialanymore. I need to create some  UPDATE ..... where owner='id of the owner' ORDER by timestamp, that will  
recalculate column 'position' to contain actual position  inside a timestamp ordered table ? (ie. colum position
containan actual order of records that is owned by 'owner' ordered by timestamp ).Please note that usage of plain
LIMIT/OFFSETis not what I need. 


in close relation to this, I have another problem. I NEED to assign bath number to records from this  example. ie in
thetable content, where owner='id of the owner' ordered by timestamp,  set of first 500 record should have the same
'bath'number  '1', set of 2nd 500 records should have its batch number '2' etc... 

Is it possible and how it can be done ?


PS: Execuse my bad english.

If you strongly require this data-behavior, you, I think, must create
function afterUpdateOrInsertOrDelete(owner), which locks owner's rows
and recalculate position and batch, if needed.
But, imho, experience says that keeping data similar to your position
(ordinal number _without_ holes) is inefficient because concurency
conflicts on paralel updates.

regards,
pajout

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.
>
>Now I need to od some select like this
>
>select * from content where owner='1000' order by timestamp with some limits, offsets etc. It is OK, no problem.
>
>Other select, like to need  select a record of user 1000  WHERE position >5 AND position <150  is OK,
>
>But now, some records are inserted, some deleted, some have the timestamp column updated, so column 'position' is not
sequentialanymore. I need to create some  UPDATE ..... where owner='id of the owner' ORDER by timestamp, that will  
>recalculate column 'position' to contain actual position  inside a timestamp ordered table ? (ie. colum position
containan actual order of records that is owned by 'owner' ordered by timestamp ).Please note that usage of plain
LIMIT/OFFSETis not what I need. 
>
>
>in close relation to this, I have another problem. I NEED to assign bath number to records from this  example. ie in
thetable content, where owner='id of the owner' ordered by timestamp,  set of first 500 record should have the same
'bath'number  '1', set of 2nd 500 records should have its batch number '2' etc... 
>
>Is it possible and how it can be done ?
>
>
>PS: Execuse my bad english.
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>

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