On Wed, Sep 25, 2019 at 3:08 PM David Salisbury <dsalis@ucar.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.