seamusabshere wrote
> hi all,
>
> This part looks correct and expected:
>
>> $ psql foobar
>> psql (9.3.5)
>> Type "help" for help.
>>
>> foobar=# select coalesce('{}'::json->'a', 1);
>> ERROR: COALESCE types json and integer cannot be matched
>> LINE 1: select coalesce('{}'::json->'a', 1);
>> ^
>
> but check it out when I use a string instead of an integer:
>
>> foobar=# select coalesce('{}'::json->'a', 'b');
>> ERROR: invalid input syntax for type json
>> LINE 1: select coalesce('{}'::json->'a', 'b');
>> ^
>> DETAIL: Token "b" is invalid.
>> CONTEXT: JSON data, line 1: b
>
> That seems like the wrong error - shouldn't it be the equiv of "[...]
> json and string cannot be matched"?
The problem here is that the literal < 'b' > is of unknown type, not a
string (varchar or text in PostgreSQL), and so in the context of COALESCE
the parser (planner?) attempts to resolve the unknown type so that the
function call succeeds.
In the first example it cannot convert a literal < 1 > to json and its
attempt to do so triggers the type mismatch error.
In the < 'b' > example there is an input routine that converts a ''-literal
to json so the parser uses that and that input routine throws the error you
see.
This isn't implicit casting per-se since if we didn't attempt to resolve the
unknown type the language would be nearly impossible to use.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Unexpected-syntax-error-when-using-JSON-in-9-3-5-tp5820141p5820149.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.