Re: json/jsonb inconsistence

On 05/29/2014 07:55 AM, Teodor Sigaev wrote:
> # select  '"\uaBcD"'::json;
>    json
> ----------
>  "\uaBcD"
>
> but
>
> # select  '"\uaBcD"'::jsonb;
> ERROR:  invalid input syntax for type json
> LINE 1: select  '"\uaBcD"'::jsonb;
>                 ^
> DETAIL:  Unicode escape values cannot be used for code point values 
> above 007F when the server encoding is not UTF8.
> CONTEXT:  JSON data, line 1: ...
>
> and
>
> # select  '"\uaBcD"'::json -> 0;
> ERROR:  invalid input syntax for type json
> DETAIL:  Unicode escape values cannot be used for code point values 
> above 007F when the server encoding is not UTF8.
> CONTEXT:  JSON data, line 1: ...
> Time: 0,696 ms
>
> More than, json looks strange:
>
> # select  '["\uaBcD"]'::json;
>     json
> ------------
>  ["\uaBcD"]
>
> but
>
> # select  '["\uaBcD"]'::json->0;
> ERROR:  invalid input syntax for type json
> DETAIL:  Unicode escape values cannot be used for code point values 
> above 007F when the server encoding is not UTF8.
> CONTEXT:  JSON data, line 1: [...
>
> Looks like random parse rules.
>


It is documented that for json we don't check the validity of unicode 
escapes until we try to use them. That's because the original json 
parser didn't check them, and if we started doing so now users who 
pg_upgraded would find themselves with invalid data in the database. The 
rules for jsonb are more strict, because we actually resolve the unicode 
escapes when constructing the jsonb object. There is nothing at all 
random about it, although I agree it's slightly inconsistent. It's been 
the subject of some discussion on -hackers previously, IIRC. I actually 
referred to this difference in my talk at pgCon last Friday.

Frankly, if you want to use json/jsonb, you are going to be best served 
by using a UTF8-encoded database, or not using non-ascii unicode escapes 
in json at all.

cheers

andrew