Keeping historical record changes w/ triggers - best practices?

From: Wells Oliver <wellsoliver(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Keeping historical record changes w/ triggers - best practices?
Date: 2013-02-06 18:41:03
Message-ID: CAOC+FBUvd9YsTgHgwJvfdQi0vd4dA+uSoccJj4WfoDMMKitmaA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)gmail(dot)com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Terence Ferraro 2013-02-06 18:44:41 Re: configuring timezone
Previous Message Igor Neyman 2013-02-06 18:32:22 configuring timezone