Can IS DISTINCT FROM be combined with ANY or ALL somehow?

Question

Is a postgres way of combining IS DISTINCT FROM with ANY or some other neat way of getting the same result?

select count(*)
from (select 'A' foo union all select 'Z' union all select null) z
where foo <> any(array[null, 'A']);

 count
-------
     1
(1 row)

select count(*)
from (select 'A' foo union all select 'Z' union all select null) z
where foo is distinct from any(array[null, 'A']);  

ERROR:  syntax error at or near "any"
LINE 3: where foo is distinct from any(array[null, 'A']);
                                   ^

Answer 1

Perhaps like this:

select foo
     , exists (values (null), ('A') except select foo) chk_any
     , not exists (values (null), ('A') intersect select foo) chk_all
from ( values ('A'),('Z'),(null) ) z(foo);

 foo | chk_any | chk_all
-----+---------+---------
 A   | t       | f
 Z   | t       | t
     | t       | f

Note that not only the null in the "array" but also the null in z is being compared this way.

Answer 2

Looking at it as a grammar problem, ANY is defined as (in Row and Array Comparisons):

expression operator ANY (array expression)

But is distinct from is not an operator, it's a "construct" as we're told in Comparison Operators:

When this behavior is not suitable, use the IS [ NOT ] DISTINCT FROM constructs

Since PostgreSQL has user-defined operators, we may define an operator/function combo for this purpose:

create function is_distinct_from(text, text) returns bool as 
'select $1 is distinct from $2;' language sql;

create operator <!> (
 procedure=is_distinct_from(text,text),
 leftarg=text, rightarg=text
);

Then it can precede ANY:

select count(*)
from (select 'A' foo union all select 'Z' union all select null) z
where foo <!> any(array[null, 'A']);  

 count 
-------
     3
(1 row)

Answer 3

Operator

This is building on @Daniel's clever operator.
While being at it, create the function/operator combo using polymorphic types. Then it works for any type - just like the construct.
And make the function IMMUTABLE.

CREATE FUNCTION is_distinct_from(anyelement, anyelement)
  RETURNS bool LANGUAGE sql IMMUTABLE AS 
'SELECT $1 IS DISTINCT FROM $2';

CREATE OPERATOR <!> (
  PROCEDURE = is_distinct_from(anyelement,anyelement),
  LEFTARG  = anyelement
, RIGHTARG = anyelement
);

A quick search with symbolhound came up empty, so the operator <!> does not seem to be in use in any of the modules.

If you are going to use this operator a lot, you might flesh it out some more to assist the query planner (like losthorse suggested in a comment). For starters, you could add the COMMUTATOR and NEGATOR clauses to assist the query optimizer. Replace CREATE OPERATOR from above with this:

CREATE OPERATOR <!> (
  PROCEDURE = is_distinct_from(anyelement,anyelement),
  LEFTARG  = anyelement
, RIGHTARG = anyelement
, COMMUTATOR = <!>
, NEGATOR = =!=
);

And add:

CREATE FUNCTION is_not_distinct_from(anyelement, anyelement)
  RETURNS bool LANGUAGE sql IMMUTABLE AS 
'SELECT $1 IS NOT DISTINCT FROM $2';

CREATE OPERATOR =!= (
  PROCEDURE = is_not_distinct_from(anyelement,anyelement),
  LEFTARG  = anyelement
, RIGHTARG = anyelement
, COMMUTATOR = =!=
, NEGATOR = <!>
);

But the additional clauses won't help with the use case at hand and plain indexes still won't be used. It's much more sophisticated to achieve that. (I haven't tried.) Read the chapter "Operator Optimization Information" in the manual for details.

Test case

The test case in the question can only succeed if all values in the array are identical. For the array in the question ('{null,A}'::text[]) the result is always TRUE. Is that intended? I added another test for "IS DISTINCT FROM ALL":

SELECT foo
     , foo <!> ANY ('{null,A}'::text[]) AS chk_any
     , foo <!> ALL ('{null,A}'::text[]) AS chk_all
FROM (
   VALUES ('A'),('Z'),(NULL)
   ) z(foo)

 foo | chk_any | chk_all
-----+---------+---------
 A   | t       | f
 Z   | t       | t
     | t       | f

Alternative with standard operators

foo IS DISTINCT FROM ANY (test_arr) -- illegal syntax

can almost be translated to

foo = ALL (test_arr) IS NOT TRUE

foo = ALL (test_arr) yields ...

TRUE .. if all elements are foo
FALSE .. if any NOT NULL element is <> foo
NULL .. if at least one element IS NULL and no element is <> foo

So, the remaining corner case is where
- foo IS NULL
- and test_arr consists of nothing but NULL elements.

If either one can be ruled out, we are done. Therefore, use the simple test if
- the column is defined NOT NULL.
- or you know the array is never all NULLs.

Else, test additionally:

AND ('A' = ALL(test_arr) IS NOT NULL OR 
     'B' = ALL(test_arr) IS NOT NULL OR
     foo IS NOT NULL)

Where 'A' and 'B' can be any distinct values. Explanation and alternatives under this related question on SO:
Is array all NULLs in PostgreSQL

Again, if you know about any value that cannot exist in test_arr, for instance the empty string '', you can still simplify:

AND ('' = ALL(test_arr) IS NOT NULL OR
     foo IS NOT NULL)

Here is a complete test matrix to check for all combinations:

SELECT foo, test_arr
     , foo = ALL(test_arr) IS NOT TRUE  AS test_simple
     , foo = ALL(test_arr) IS NOT TRUE
       AND ('A' = ALL(test_arr) IS NOT NULL OR
            'B' = ALL(test_arr) IS NOT NULL OR 
            foo IS NOT NULL)            AS test_sure 
FROM (
   VALUES ('A'),('Z'),(NULL)
   ) v(foo)
CROSS JOIN (
   VALUES ('{null,A}'::text[]),('{A,A}'),('{null,null}')
   ) t(test_arr)

 foo |  test_arr   | test_simple | test_sure
-----+-------------+-------------+-----------
 A   | {NULL,A}    | t           | t
 A   | {A,A}       | f           | f   -- only TRUE case
 A   | {NULL,NULL} | t           | t
 Z   | {NULL,A}    | t           | t
 Z   | {A,A}       | t           | t
 Z   | {NULL,NULL} | t           | t
     | {NULL,A}    | t           | t
     | {A,A}       | t           | t
     | {NULL,NULL} | t           | f   -- special case

This is a bit more verbose than Andriy's EXCEPT solution, but it is substantially faster.