Re: Arrays and ANY problem

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

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