Czy można w jakiś sposób połączyć WYRÓŻNIENIE Z KAŻDEJ lub WSZYSTKIEJ?

Czy postgres jest sposobem na połączenie IS DISTINCT FROMz ANYinnym sposobem na uzyskanie tego samego rezultatu?

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

Być może tak :

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

Zauważ, że w ten sposób porównywane są nie tylko null„tablice”, ale także „ nullin” z.

Patrząc na to jako problem gramatyczny, ANYdefiniuje się jako (w porównaniach wierszy i macierzy ):

operator wyrażenia DOWOLNY (wyrażenie tablicowe)

Ale is distinct fromto nie jest operator, to „konstrukcja”, jak powiedziano w Operatory porównania :

Kiedy takie zachowanie nie jest odpowiednia, należy użyć IS [NIE] odróżnieniu od konstrukcji

Ponieważ PostgreSQL ma operatory zdefiniowane przez użytkownika, możemy w tym celu zdefiniować kombinację operator / funkcja:

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
);

Następnie może poprzedzać ANY:

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

 liczyć 
-------
     3)
(1 rząd)

Operator

Opiera się to na sprytnym operatorze @ Daniela .
Będąc przy tym, utwórz kombinację funkcji / operatora, używając typów polimorficznych . Następnie działa dla każdego typu - podobnie jak konstrukcja.
I wykonaj tę funkcję 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
);

Szybkie wyszukiwanie z symbolhoundem było puste, więc operator <!>nie wydaje się być używany w żadnym module.

Jeśli zamierzasz często korzystać z tego operatora, możesz go trochę dopracować, aby pomóc planerowi zapytań ( jak sugerowany w komentarzu Losthorse ). Na początek możesz dodać klauzule COMMUTATORi NEGATOR, aby wspomóc optymalizator zapytań. Zamień CREATE OPERATORz góry na:

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

I dodaj:

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 = <!>
);

Ale dodatkowe klauzule nie pomogą w danym przypadku użycia, a zwykłe indeksy nadal nie będą używane. Osiągnięcie tego jest znacznie bardziej wyrafinowane. (Nie próbowałem.) Aby uzyskać szczegółowe informacje, przeczytaj rozdział „Informacje dotyczące optymalizacji operatora” w podręczniku.

Przypadek testowy

Przypadek testowy w pytaniu może się powieść tylko wtedy, gdy wszystkie wartości w tablicy są identyczne. Dla tablicy w pytaniu ( '{null,A}'::text[]) wynik jest zawsze PRAWDA. Czy to jest zamierzone? Dodałem kolejny test dla „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

Alternatywa dla standardowych operatorów

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

można prawie przetłumaczyć na

foo = ALL (test_arr) IS NOT TRUE

foo = ALL (test_arr) daje ...

TRUE .. jeśli wszystkie elementy są foo
FALSE.. jeśli jakikolwiek NOT NULLelement jest <> foo
NULL .. jeśli przynajmniej jeden element IS NULLi żaden element nie jest<> foo

Tak więc pozostały przypadek narożny jest tam, gdzie
- foo IS NULL
- i test_arr składa się tylko z NULLelementów.

Jeśli jedno z nich można wykluczyć, to koniec. Dlatego skorzystaj z prostego testu, jeśli
- kolumna jest zdefiniowana NOT NULL.
- czy ty wiesz, tablica nie jest wszystkie wartości null.

W przeciwnym razie przetestuj dodatkowo:

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

Gdzie 'A'i 'B'mogą być dowolne odrębne wartości. Wyjaśnienie i alternatywy w ramach tego pokrewnego pytania na temat SO:
Czy tablica zawiera wszystkie wartości NULL w PostgreSQL

Ponownie, jeśli znasz dowolną wartość, która nie może istnieć test_arr, na przykład pusty ciąg '', nadal możesz uprościć:

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

Oto pełna matryca testowa do sprawdzenia wszystkich kombinacji:

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

Jest to nieco bardziej szczegółowe niż rozwiązanie AndriyaEXCEPT , ale jest znacznie szybsze.