Fwd: Advice on moving rows to history

Meant this to go to whole list , sorry for duplicate

Begin forwarded message:

>
> On Dec 29, 2004, at 4:58 PM, Dave Smith wrote:
>
>> I am trying to figure out the fastest way to move rows from a current
>> table to a history table. There are currently 150,000 rows of which
>> about 60,000 get moved (monthly). There are multiple queries involved
>> to
>> figure out whether or not a row should be moved, so I am making
>> multiple
>> passes over the table.
> But no matter what, if all candidates for removal are to be treated in
> the same way . . .
>
>> Right now I see two options
>>
>> 1) Write a function that inserts a row into the history table and then
>> removes it from the current
> Create a trigger BEFORE a DELETE event that moves these items to the
> history table
>
> as explained better here:
>
> http://www.postgresql.org/docs/current/static/triggers.html
>
>> 2) Have a status flag that marks the rows for history and then insert
>> them into the history table then remove all of these rows from the
>> current.
>>
>> I would like to know what other peoples experiences have been with
>> this
>> problem, and what would you suggest.
>>
>>
>> --
>> Dave Smith
>> CANdata Systems Ltd
>> 416-493-9020
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to
>> majordomo(at)postgresql(dot)org)