Search code examples
sqlgrammaroperator-precedence

Operator precedence of `EXISTS`

In Postgres, does the EXISTS operator have the highest precedence of all? For example:

SELECT 1 + EXISTS (SELECT 1)::int;

It seems to be missing from the manual page. Though the highest one is ::, meaning EXISTS would be higher?

In other words, it evaluates it like this:

select (exists (select 1))::int;

And not like this, which, if you try, will error:

select exists ((select 1)::int);

Meaning that the EXISTS operator has higher precedence than the CAST operator. Is this correct?

Note: adding a bounty and changing this from SQL to any RDBMS that supports EXISTS (which I believe is all mainstream?)

Solution

  • The elephant in the room: EXISTS is not an operator in Postgres, technically speaking.

    It's a syntax element. The manual lists it in the chapter "Subquery Expressions". Operator precedence does not apply. EXISTS "binds" before any operator. The Postgres source code uses the term "SubLink" for this kind of plan node.

    Accordingly, you'll also find no corresponding entry in pg_operator.

    Also accordingly, in your example, :: ranking number 2 in operator precedence doesn't make the cut. Your test isn't ideal, though. Either precedence would result in integer 1:

    SELECT EXISTS (SELECT 1)::int;

    This is unambiguous:

    SELECT EXISTS (SELECT 'foo')::int;

    Would error out, if ::int took precedence - like this does:

    --  invalid input syntax for type integer: "foo"
SELECT        (SELECT 'foo')::int;

    In case you still want more proof: even the number 1 in the operator precedence ranking, the column separator (.), can't overcome the gravitational pull of EXISTS:

    CREATE  TABLE t (id int);

SELECT        (SELECT t FROM t LIMIT 1).id;  -- valid
SELECT EXISTS (SELECT t FROM t LIMIT 1).id;  -- invalid!

    fiddle

    The second SELECT would try to append .id to the boolean value true, which is a syntax error.