Обсуждение: Unexpected syntax error when using JSON -> in 9.3.5

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"?

Thanks,
Seamus


--
Seamus Abshere, SCEA
https://github.com/seamusabshere

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.

Seamus Abshere <seamus@abshere.net> writes:
>> foobar=# select coalesce('{}'::json->'a', 1);
>> ERROR:  COALESCE types json and integer cannot be matched

Right ...

> 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

This is expected.  The parser correctly determines that the json->text
operator returns json, and it knows that the two arguments of coalesce
must resolve as the same type, so it tries to coerce the untyped literal
to json --- which means passing that string to the json input function.
A quoted literal is not automatically a string in Postgres, it can get
resolved as some other type if required by context.  In the first example
the integer literal has a well-defined type, but bare quoted literals are
not considered to have a known type.

            regards, tom lane