Обсуждение: any with the output of coalesce

I am trying to create an expression which
- always yield true if the incomming array is NULL
- yields true if a given value is in the array, otherwise yields false

I thought this should work:
Select 'target'=ANY(COALESCE('{indata1, indata2}','{target}'))

but I get an ERROR:  op ANY/ALL (array) requires array on right side
Can somebody tell me what I am doing wrong and how to do this right?

Thanks for the help.
Balázs

> I am trying to create an expression which
> - always yield true if the incomming array is NULL
> - yields true if a given value is in the array, otherwise yields false
>
> I thought this should work:
> Select 'target'=ANY(COALESCE('{indata1, indata2}','{target}'))
>
> but I get an ERROR:  op ANY/ALL (array) requires array on right side
> Can somebody tell me what I am doing wrong and how to do this right?

Read up on array input syntax
(http://www.postgresql.org/docs/current/static/arrays.html). What you
are giving as input to your coalesce above are two strings, not two
arrays. Try this:

select 'target' = any(coalesce('{"indata1","target"}'::text[],
'{"target"}'::text[]));

I personally find the ARRAY syntax more intuitive:

select 'target' = any(coalesce(array['indata1','indata2'],
array['target']));

This will work as i think you expect it to. Note that array behavior
with respect to nulls changed with 8.2 so whether "incomming array is
NULL" will differ depend on your version.