Re: Arrays and ANY problem

[David Salisbury <dsalis@xxxxxxxx>]

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@xxxxxxxxxxx> 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
);