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[]) );
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.
