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