An interesting quirk:
# select CASE WHEN '{"a":null}'::jsonb->>'a' IS NULL THEN 'yes' ELSE 'no' END;
case
------
yes
According to the precedence table http://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html I would expect ->> to come under "all other native and user-defined operators", which would imply that this command should be testing whether 'a' IS NULL and applying the result (false) to the json operator - at which point we have
# SELECT CASE WHEN '{"a":null}'::jsonb->>false THEN 'yes' ELSE 'no' END;
and since
# SELECT '{"a":null}'::jsonb->>false;
# SELECT '{"a":null}'::jsonb->>false;
returns NULL, the query is effectively:
# SELECT CASE WHEN NULL THEN 'yes' ELSE 'no' END;
which returns 'no'.
So the only way that we should get 'yes' is if the ->> has higher precedence than 'IS NULL'.
OK, so be it; except if we assume that the reason is because the lex analyzer sees '-' and assumes higher precedence than 'IS NULL' then you would expect
SELECT '{"a":10}'::jsonb->>'a' - 5;
to return '5' - since left-to-right precedence would make ->> run before the subtraction; however I get:
ERROR: invalid input syntax for integer: "a"
LINE 1: select '{"a":10}'::jsonb->>'a' - 5;
So what precedence level is ->> actually running at?
Or am I missing something?
Cheers
Geoff
