Hacker News Reader

Comment by netcraft | Hacker News Reader

So we do something similar, using HSTORE to diff the old and new records, which then allows us to easily tell what was changed in the case of an update. Useful for many kinds of things, but especially forensics.

View on HN

I have a usecase where I could use a diff of records changed, and what was changed. I don't see how HSTORE solves this for you. Could you explain more?

In my example, say a user edits their document and saves it (to a row in the DB). I want to be able to tell what their previous row was, what the new one is, and basically git diff them.

I replied to a sister comment with an example. HSTORE can take a whole row and turn it into an HSTORE value, plus HSTORE has an operator of `-` that will diff two HSTORE values. So combine that with the OLD and NEW that you get in the trigger and you can effectively diff the records.

clever! hadn't thought of combining this with hstore.

``` , SUBSTRING(TG_OP, 1, 1) -- this gets you I,D,U, or T for truncate

    , HSTORE(oldData.*) - HSTORE(newData.*)

    , HSTORE(newData.*) - HSTORE(oldData.*)

``` this is basically what we do, getting the old and new of only what changed.

Reply on news.ycombinator.com