Re: Arrays and ANY problem

Alban Hertroys <haramrae(at)gmail(dot)com> writes:
>> On 25 Sep 2019, at 22:50, Alban Hertroys <haramrae(at)gmail(dot)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