I have a wide-ish table with 60 columns. I want to make a copy of data
whenever a record is updated or deleted.
Right now I have a table that's almost identical but with a 'created'
column (timestamp) and an 'action' column (which gets TG_OP for UPDATE or
DELETE).
My idea would be to sort on the created column to see the historical record
by comparing the columns. My other thought is to create two columns for
each column in the master table (old_column, new_column, etc), storing the
old values and the new values, and see what's changed that way.
The other idea, probably a terrible idea, was to use hstore to create a
list of the old values and new values, and have this history table just be
the timestamp, action, and two hstore columns.
Surely this has been done thousands of times. What are the thoughts
regarding best practices in PG?
Thanks everyone.
--
Wells Oliver
wellsoliver@gmail.com