Re: Arrays and ANY problem

On Wed, Sep 25, 2019 at 3:08 PM David Salisbury <dsalis(at)ucar(dot)edu> wrote:

> Thanks,
>
> Unfortunately I believe I need to include a postgres module to get the
> "<@" operator, which I have no power to do. This is what I get with that
> operator..
>
> select name from table_name_ds_tmp where categoryid <@ ANY ( ARRAY[ 200,
> 400]::BIGINT[] );
> ERROR: operator does not exist: bigint <@ bigint
>

The "<@" operator is standard...you were even provided an example of how to
use it. That its doesn't work when you do something different isn't
surprising.

> The second query does work, but in the end I'll need to have a select in
> that area to pick out my numbers, can't hard code it, and that seems to be
> what screws my query up, the select, and that makes no sense.
>

Then how about providing what you will eventually need so people aren't
wasting their time with stuff you won't be able to use.

There are two "ANY" constructs documented. One covers a subquery and one
encapsulates an array. The presence of "select" forces the subquery
interpretation even if the select just happens to be providing a set of
arrays (a set of cardinality one).

> Here's what happens without the ARRAY wrapping around categoryid, as it
> your second thought...
>
> select name from table_name_ds_tmp where categoryid = ANY ( select
> string_to_array( '200,400', ',')::bigint[] );
> ERROR: operator does not exist: bigint = bigint[]
>
> At least it runs with ARRAY[categoryid], it just doesn't return anything.
> :-(
>

This is all documented and the specific reason this doesn't match has been
previously explained in this thread.

See:

https://www.postgresql.org/docs/11/functions-subquery.html#FUNCTIONS-SUBQUERY-ANY-SOME

compared to

https://www.postgresql.org/docs/11/functions-comparisons.html#id-1.5.8.28.16

David J.