4 ms·
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
by netcraft 5y ago
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.
- kmdupree 5y agoclever! hadn't thought of combining this with hstore.
- netcraft 5y ago``` , 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.
- PenguinCoder 5y agoI 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.
- netcraft 5y agoI 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.