[GENERAL] Unexpected syntax error when using JSON - in 9.3.5

2014-09-23 Thread Seamus Abshere 

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unexpected syntax error when using JSON - in 9.3.5

2014-09-23 Thread David G Johnston 
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.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unexpected syntax error when using JSON - in 9.3.5

2014-09-23 Thread Tom Lane 
Seamus Abshere sea...@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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general