Unexpected syntax error when using JSON -> in 9.3.5

[Seamus Abshere]

hi all,

$ 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);
^
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

[David G Johnston]

Post by Seamus Abshere
hi all,

$ 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);
^
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.

[Tom Lane]

foobar=# select coalesce('{}'::json->'a', 1);
ERROR: COALESCE types json and integer cannot be matched

Right ...

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