Re: json ->> operator precedence

Geoff Winkless <pgsqladmin@geoff.dj> writes:
> An interesting quirk:
> # select CASE WHEN '{"a":null}'::jsonb->>'a' IS NULL THEN 'yes' ELSE 'no'
> END;
>  case
> ------
>  yes

Apparently you're running that on 9.5 or HEAD.

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

It does ...

> 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

... and in 9.4 that's what happens:

regression=# select CASE WHEN '{"a":null}'::jsonb->>'a' IS NULL THEN 'yes' ELSE 'no'
END;
ERROR:  operator does not exist: jsonb ->> boolean
LINE 1: select CASE WHEN '{"a":null}'::jsonb->>'a' IS NULL THEN 'yes...
                                            ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

> Or am I missing something?

The first compatibility item in the 9.5 release notes: we changed
the precedence of IS and some other things.  You need to be reading
the 9.5 version of the precedence table.

            regards, tom lane