Re:Unstructured Datatypes in Postgres – Hstore vs. JSON vs. ...
by chrismorgan on Wednesday July 20, 2016 @09:54PM
(#12134054)
I’ve wondered before: why does hstore still exist? I get that JSON adds something over JSONB, but as far as I can tell (without ever having used either of them) hstore functionality is strictly inferior to JSONB; if that is the case, why would hstore not be deprecated and marked for eventual removal?
[ Parent
| on HN ]
Re:Unstructured Datatypes in Postgres – Hstore vs. JSON vs. ...
by craigkerstiens on Wednesday July 20, 2016 @09:58PM
(#12134077)
Today hstore does still have one thing over JSONB which is GiST indexing. Yes, normally GIN is still preferred but there are cases with you might want to use GiST. I do expect that JSONB will get GiST index support as well, at that time there really becomes no reason at all to use hstore over JSONB.
Re:Unstructured Datatypes in Postgres – Hstore vs. JSON vs. ...
by ht85 on Thursday July 21, 2016 @01:02AM
(#12134809)
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.
Re:Unstructured Datatypes in Postgres – Hstore vs. JSON vs. ...
by chrismorgan on Thursday July 21, 2016 @01:21AM
(#12134908)
Any real reason why JSONB shouldn’t support those operators? (Does it do something else with them?)
Re:Unstructured Datatypes in Postgres – Hstore vs. JSON vs. ...
by ht85 on Thursday July 21, 2016 @01:48AM
(#12135006)
hstore 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.