Re: Differences between = ANY and IN?

Maciek Sakrejda <m.sakrejda@gmail.com> writes:
> My colleague's patch changes SQL generated from Ruby expressions like
> `where(id: [1, 2])` . This is currently translated to roughly `WHERE
> id IN (1, 2)` and would be changed to `id = ANY('{1,2}')`.

> As far as we know, the expressions are equivalent, but we wanted to
> double-check: are there any edge cases to consider here (other than
> the pg_stat_statements behavior, of course)?

You would find it profitable to read transformAExprIn() in parse_expr.c.
The most important points are in this comment:

     * We try to generate a ScalarArrayOpExpr from IN/NOT IN, but this is only
     * possible if there is a suitable array type available.  If not, we fall
     * back to a boolean condition tree with multiple copies of the lefthand
     * expression.  Also, any IN-list items that contain Vars are handled as
     * separate boolean conditions, because that gives the planner more scope
     * for optimization on such clauses.

If all the values in the IN form were being sent to the backend as
constants of the same datatype, I think you're okay to consider it
as exactly equivalent to =ANY.  It would likely be a good idea to
provide an explicit cast `id = ANY('{1,2}'::int[])` rather than just
hoping an unadorned literal will be taken as the type you want
(see transformAExprOpAny and thence make_scalar_array_op).

            regards, tom lane