On Wed, Feb 6, 2013 at 12:41 PM, Wells Oliver <wellsoliver@xxxxxxxxx> wrote: > 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? I handle this using middleware outside the db. Past revisions of a record (from any table I want to track) are serialized into a JSON format and stored in a single table. Postgres speaks JSON now, so.. -- Greg Donald -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
