patternsqlMajor
Can IS DISTINCT FROM be combined with ANY or ALL somehow?
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,
(in Row and Array Comparisons):
expression operator ANY (array expression)
But
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:
Then it can precede
count
-------
3
(1 row)
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.