* Robert Haas (robertmh...@gmail.com) wrote: > On Thu, Jan 29, 2015 at 5:30 PM, Andrew Dunstan <and...@dunslane.net> wrote: > > jsonb stores string values as postgres text values, with the unicode escapes > > resolved, just as it also resolves numbers and booleans into their native > > representation. If you want the input perfectly preserved, use json, not > > jsonb. I think that's made pretty clear in the docs. > > > > so text->jsonb->text is not and has never been expected to be a noop. > > If you can't store text in a jsonb object and get it back out again, > it doesn't seem like a very useful data type.
Hrm. That sentence strikes me as slightly confused. You *can* store text in a jsonb object and get it back out again, however, that's not the same as taking a JSON structure which is represented as text, converting it to jsonb, and then converting it back into a text representation. > Where exactly do you think this is documented? http://www.postgresql.org/docs/9.4/static/datatype-json.html "Because the json type stores an exact copy of the input text, it will preserve semantically-insignificant white space between tokens, as well as the order of keys within JSON objects. Also, if a JSON object within the value contains the same key more than once, all the key/value pairs are kept. (The processing functions consider the last value as the operative one.) By contrast, jsonb does not preserve white space, does not preserve the order of object keys, and does not keep duplicate object keys. If duplicate keys are specified in the input, only the last value is kept." There is further discussion about how the JSON primitive types are mapped on to native PostgreSQL types, which both constrains what values are valid and may impact the formatting of values, eg: "One semantically-insignificant detail worth noting is that in jsonb, numbers will be printed according to the behavior of the underlying numeric type. In practice this means that numbers entered with E notation will be printed without it, for example:" That, overall, isn't really the issue at hand though, from what I gather. The problem is that strings can be represented in JSON which can't be represented in our 'text' datatype and therefeore in our jsonb type (since jsonb converts it to a 'text' datatype underneath). There were attempts to make it work anyway, but that ended up causing other issues. In the end, I tend to agree with Tom on this one and, while it really stinks to break things for folks who have just started using 9.4, we're at least early enough on that we hopefully won't break it for *too* many people. Thanks, Stephen
signature.asc
Description: Digital signature
