Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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']);
                                   ^
share|improve this question
up vote 7 down vote accepted

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.

share|improve this answer

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)
share|improve this answer
1  
Excellent, insightful answer. – Erwin Brandstetter Mar 7 '14 at 5:40
    
This is definitely much superior to the workaround I suggested, especially with @Erwin's improvement. – Andriy M Mar 7 '14 at 7:47
    
This answer and @Erwin's suggested tweaks are really excellent. I'm accepting Andriy's but it is just a case of personal preference: I'm sure many will prefer the elegance of yours. – Jack Douglas Mar 7 '14 at 16:16
    
@JackDouglas: I added an alternative solution with standard operators. – Erwin Brandstetter Mar 7 '14 at 19:45

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.

share|improve this answer
    
When creating the OPERATOR, should the COMMUTATOR (and NEGATOR, perhaps with the inverse IS NOT DISTINCT FROM operator) clause be supplied? postgresql.org/docs/current/static/xoper-optimization.html – losthorse Dec 2 '15 at 14:56
1  
@losthorse: I added a bit addressing that. – Erwin Brandstetter Dec 2 '15 at 18:27

protected by Max Vernon Apr 19 at 18:50

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.