Thread: Arrays and ANY problem

Arrays and ANY problem

From

David Salisbury

Date:

25 September 2019, 23:25:15

Perhaps someone can guide me here as I'm having a "moment". :)

Not sure why I am getting 0 rows returned here:

(0 rows)

I would expect, and am in need of, the query returning these 2 rows,

"two"

"four"

which I plan to aggregate together later on with array_to_string.

Basically I hope to take a string and substitute the values in it with their associated values.

Other incantations and permutations just seem to give me various error messages.

Thanks for any help.

Re: Arrays and ANY problem

From

Michael Lewis

Date:

25 September 2019, 23:48:22

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

Re: Arrays and ANY problem

From

Alban Hertroys

Date:

25 September 2019, 23:50:03

> On 25 Sep 2019, at 22:25, David Salisbury <dsalis@ucar.edu> wrote:
> db=# select name from table_name_ds_tmp where ARRAY[categoryid] = ANY ( select string_to_array( '200,400',
',')::bigint[]); 
>  name
> ------
> (0 rows)

You are comparing two arrays for equality. Since the left-hand array has only 1 item and the right-hand one has two,
there’snot much equality between them. 

You probably meant:
select name from table_name_ds_tmp where categoryid = ANY ( select string_to_array( '200,400', ',')::bigint[] );


Alban Hertroys
--
There is always an exception to always.

Re: Arrays and ANY problem

From

Alban Hertroys

Date:

25 September 2019, 23:54:49

> On 25 Sep 2019, at 22:50, Alban Hertroys <haramrae@gmail.com> wrote:
>
>
>> On 25 Sep 2019, at 22:25, David Salisbury <dsalis@ucar.edu> wrote:
>> db=# select name from table_name_ds_tmp where ARRAY[categoryid] = ANY ( select string_to_array( '200,400',
',')::bigint[]); 
>> name
>> ------
>> (0 rows)
>
> You are comparing two arrays for equality. Since the left-hand array has only 1 item and the right-hand one has two,
there’snot much equality between them. 
>
> You probably meant:
> select name from table_name_ds_tmp where categoryid = ANY ( select string_to_array( '200,400', ',')::bigint[] );

Or rather:
select name from table_name_ds_tmp where categoryid = ANY ( string_to_array( '200,400', ',')::bigint[] );

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Re: Arrays and ANY problem

From

David Salisbury

Date:

26 September 2019, 01:07:40

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@entrata.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
);

Re: Arrays and ANY problem

From

"David G. Johnston"

Date:

26 September 2019, 01:27:41

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.

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.

Re: Arrays and ANY problem

From

Tom Lane

Date:

26 September 2019, 01:38:25

Alban Hertroys <haramrae@gmail.com> writes:
>> On 25 Sep 2019, at 22:50, Alban Hertroys <haramrae@gmail.com> wrote:
>> You probably meant:
>> select name from table_name_ds_tmp where categoryid = ANY ( select string_to_array( '200,400', ',')::bigint[] );

> Or rather:
> select name from table_name_ds_tmp where categoryid = ANY ( string_to_array( '200,400', ',')::bigint[] );

Yeah, this is fairly confusing, because there are multiple different
features with barely distinguishable syntaxes here.  You can do

    value = ANY (SELECT ...)

which compares "value" to each row of the sub-SELECT result (and the
sub-SELECT had better return one column, of a type comparable to
"value").  Or you can do

    value = ANY (array-expression)

which compares "value" to each element of the array value (which had
better have elements of a type comparable to "value").  What you
can't do is generate the array value from a sub-select, because that
will be taken as being an instance of the first feature.

David didn't say what his real problem was, but I'd suggest that
making his sub-select return a rowset result rather than an array
result might be the best way to resolve things.  It's more SQL-y,
for sure.

            regards, tom lane

Re: Arrays and ANY problem

From

David Salisbury

Date:

30 September 2019, 21:23:27

I didn't specify the real problem as it's all wrapped up in layers and I didn't want to post a "can someone write the query for me". The real problem was I have a table with a string holding comma separated numbers, and needed to go to a lookup table and replace each of those numbers with it's correlated value. So '12,2,10' gets converted to 'twelve,two,ten'.

Tom's "I'd suggest that making his sub-select return a rowset result rather than an array" was spot on and lead me to "unnest". For my posted problem this was the simple solution. Sorry to narrow things down to my specific array method.

select name from table_name_ds_tmp where categoryid = ANY ( select unnest(string_to_array( '200,400', ',')::bigint[]) );

Thanks everyone for the help!

-ds

On Wed, Sep 25, 2019 at 4:38 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Alban Hertroys <haramrae@gmail.com> writes:
>> On 25 Sep 2019, at 22:50, Alban Hertroys <haramrae@gmail.com> wrote:
>> You probably meant:
>> select name from table_name_ds_tmp where categoryid = ANY ( select string_to_array( '200,400', ',')::bigint[] );

> Or rather:
> select name from table_name_ds_tmp where categoryid = ANY ( string_to_array( '200,400', ',')::bigint[] );

Yeah, this is fairly confusing, because there are multiple different
features with barely distinguishable syntaxes here. You can do

value = ANY (SELECT ...)

which compares "value" to each row of the sub-SELECT result (and the
sub-SELECT had better return one column, of a type comparable to
"value"). Or you can do

value = ANY (array-expression)

which compares "value" to each element of the array value (which had
better have elements of a type comparable to "value"). What you
can't do is generate the array value from a sub-select, because that
will be taken as being an instance of the first feature.

David didn't say what his real problem was, but I'd suggest that
making his sub-select return a rowset result rather than an array
result might be the best way to resolve things. It's more SQL-y,
for sure.

regards, tom lane

Re: Arrays and ANY problem

From

Andrew Gierth

Date:

30 September 2019, 21:55:00

>>>>> "David" == David Salisbury <dsalis@ucar.edu> writes:

 David> I didn't specify the real problem as it's all wrapped up in
 David> layers and I didn't want to post a "can someone write the query
 David> for me". The real problem was I have a table with a string
 David> holding comma separated numbers, and needed to go to a lookup
 David> table and replace each of those numbers with it's correlated
 David> value. So '12,2,10' gets converted to 'twelve,two,ten'.

 David> Tom's "I'd suggest that making his sub-select return a rowset
 David> result rather than an array" was spot on and lead me to
 David> "unnest". For my posted problem this was the simple solution.
 David> Sorry to narrow things down to my specific array method.

 David> select name from table_name_ds_tmp where categoryid = ANY ( select
 David> unnest(string_to_array( '200,400', ',')::bigint[]) );

This won't necessarily preserve the order of elements (it might
sometimes look like it does, but it's fooling you). It also won't handle
duplicate numbers.

The right solution that does preserve order would be:

select name
  from unnest(string_to_array( '200,400', ',')::bigint[])
         with ordinality as u(id,ord)
       join table_name_ds_tmp t on (t.category_id=u.id)
 order by u.ord;

(wrap an ARRAY( ) around that if you need the result as a single array
rather than as rows, or use string_agg(name, ',' order by ord) if you
want a comma-separated string result)

regexp_split_to_table might be a better method than
unnest/string_to_array.

-- 
Andrew (irc:RhodiumToad)