HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMajor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
distinctcancombinedsomehowallwithanyfrom

Problem

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']);
                                   ^

Solution

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)

Code Snippets

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

Context

StackExchange Database Administrators Q#60342, answer score: 21

Revisions (0)

No revisions yet.