Advice on moving rows to history

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@postgresql.org)