4 ms·
hstore has some stuff that's missing from JSON. My favorite hstore feature is that you can produce a diff simply by using the minus operator. For auditing a ta
by ht85 10y ago
hstore has some stuff that's missing from JSON.
My favorite hstore feature is that you can produce a diff simply by using the minus operator. For auditing a table, in an ON UPDATE trigger you can simply do
previous_values = hstore(OLD) - hstore(NEW)
previous_values will only contain columns that have changed, with their old values.
You can then restore the row to its previous state by doing PREVIOUS_ROW = CURRENT_ROW #= previous_values.
- chrismorgan 10y agoAny real reason why JSONB shouldn’t support those operators? (Does it do something else with them?)
- ht85 10y agohstore are very different from JSON in the sense that they are a flat key/value structure, which is exactly what a record/row in the database is. This is why you have operators to convert and combine hstore values and actual records. The ability to diff might come at some point, but interest for it is probably low because of the purpose of JSONB. Diffing works best for similar structure, and if you're storing things with similar structure in postgres, I doubt you're going to use JSONB. Diffing deep trees is also very costly.