Re:Unstructured Datatypes in Postgres – Hstore vs. JSON vs. ...
by koolba on Wednesday July 20, 2016 @10:16PM
(#12134142)
One other (big) difference between json and jsonb is that the former preserves extra white space. The exact text string of the json is stored. If that matters to you, say you're taking a signature of the original value, then you should use non-b json. Otherwise, stick to jsonb.
[ Parent
| on HN ]
Re:Unstructured Datatypes in Postgres – Hstore vs. JSON vs. ...
by ht85 on Thursday July 21, 2016 @01:10AM
(#12134854)
Probably the biggest gotcha of JSON is that there is no equality operator implemented for it. The reason is the philosophy of postgres to never do anything if you don't do it right, and you can't compare JSON as strings because of key order and whitespace.
In itself it doesn't seem like a big deal, but you'll lose generic things that normally always work. An example would be a trigger that updates an updated_at column, by checking
IF OLD IS DISTINCT FROM NEW
This syntax normally works on everything, but will throw if a column is of type JSON.
Re:Unstructured Datatypes in Postgres – Hstore vs. JSON vs. ...
by agwa on Wednesday July 20, 2016 @10:51PM
(#12134280)
An even bigger difference is that with jsonb, strings and numbers are converted to their equivalent PostgreSQL types. That means that strings can't contain null characters or characters that violate the database's encoding rules. Consequentially, not all valid JSON documents can be stored in jsonb, which is usually fine but can sometimes introduce headaches.
Re:Unstructured Datatypes in Postgres – Hstore vs. JSON vs. ...
by dexwiz on Wednesday July 20, 2016 @10:43PM
(#12134260)
Why does whitespace (outside of string values) ever matter in JSON? Most servers will strip as much whitespace as possible to reduce response size. Also stripping before signing is pretty trivial.
Re:Unstructured Datatypes in Postgres – Hstore vs. JSON vs. ...
by angryasian on Wednesday July 20, 2016 @11:06PM
(#12134336)
just throwing something out, but if you're allowing users to enter in their own json.
Re:Unstructured Datatypes in Postgres – Hstore vs. JSON vs. ...
by dexwiz on Wednesday July 20, 2016 @11:08PM
(#12134347)
Why would you want to maintain extra whitespace (again outside of text strings) after a save? Any sort of JSON editor is going to have some sort of formatting function that could restore it on load.
Re:Unstructured Datatypes in Postgres – Hstore vs. JSON vs. ...
by yeukhon on Wednesday July 20, 2016 @11:53PM
(#12134512)
One exmaple: implement a text editor like GitHub's gist naively.
You can try to be clever and encode the incoming data to reduce whitespace, but the business requirement is whatever user entered is what user expects to get back. Otherwise, your best bet is save the entire text.
Re:Unstructured Datatypes in Postgres – Hstore vs. JSON vs. ...
by rvense on Thursday July 21, 2016 @04:52AM
(#12135598)
But in that case, should you really be storing the JSON as JSON? Isn't it just a string as far as you're concerned? You're not going to be querying the user's JSON, are you?
Re:Unstructured Datatypes in Postgres – Hstore vs. JSON vs. ...
by yeukhon on Thursday July 21, 2016 @07:45PM
(#12140850)
You are correct and it depends on how you want to save your data (poor or bad!). In the article it does use log as an example. Perhaps that would be a more convincing-ish example.