Re: Arrays and ANY problem

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 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.

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.
:-((

On Wed, Sep 25, 2019 at 2:48 PM Michael Lewis <mlewis(at)entrata(dot)com> wrote:

> db=# select name from table_name_ds_tmp where ARRAY[categoryid] = ANY (
>> select string_to_array( '200,400', ',')::bigint[] );
>>
>
> Using either of the below instead, I get the proper result. Why doesn't
> ANY work? I do not know.
>
> select name from table_name_ds_tmp where ARRAY[categoryid] <@ ( select
> (string_to_array( '200,400', ','))::bigint[] );
> select name from table_name_ds_tmp where categoryid = ANY ( ARRAY[ 200,
> 400]::BIGINT[] );
>
> I used-
>
> drop table if exists pg_temp.table_name_ds_tmp;
> create temp table table_name_ds_tmp AS(
> SELECT 100::BIGINT AS categoryid, 'one'::VARCHAR AS name UNION ALL
> SELECT 200::BIGINT, 'two'::VARCHAR UNION ALL
> SELECT 300::BIGINT, 'three'::VARCHAR UNION ALL
> SELECT 400::BIGINT, 'four'::VARCHAR
> );
>