I don't mean to hog my own thread, but the more I look at the hstore type, the more reasonable it seems. The table is just a serial, a timestamp, and two columns 'old' and 'new'. The trigger function inserts these values using hstore(OLD) and hstore(NEW).
Then, you can select old, new, and new - old, which returns an hstore of what changed, or you could store this as a third column called 'delta' or whatever. The hstore of course can be cast to a record, or any other suitable object.
Of course, you could not store old and new, and only the delta if you preferred, but it's nice to have both records.
Will anyone tell me there's some terrible side effect of this approach that I am not realizing?
On Wed, Feb 6, 2013 at 11:10 AM, Greg Donald <gdonald@xxxxxxxxx> wrote:
I handle this using middleware outside the db. Past revisions of aOn 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?
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
Wells Oliver
wellsoliver@xxxxxxxxx
