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