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
