DISTINCT FROM을 ANY 또는 ALL과 결합 할 수 있습니까? select null) z where foo

결합 포스트 그레스 방법 IS DISTINCT FROM으로 ANY또는 동일한 결과를 얻기의 다른 깔끔한 방법은?

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

답변

아마도 이것 처럼 :

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

뿐만 아니라 있습니다 null은 “배열”에서뿐만 아니라 null에서이 z이런 식으로 비교되고있다.

답변

문법 문제로 보았을 때 행과 배열 비교ANY 에서 다음과 같이 정의됩니다 .

식 연산자 ANY (배열 식)

그러나 is distinct from연산자는 아니지만 비교 연산자 에서 알 수 있듯이 “구문”입니다 .

이 동작이 적합하지 않은 경우 IS [NOT] DISTINCT FROM
구문을 사용하십시오.

PostgreSQL에는 사용자 정의 연산자가 있으므로 다음과 같은 목적으로 연산자 / 함수 콤보를 정의 할 수 있습니다.

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

그런 다음 앞에 올 수 있습니다 ANY.

select count(*)
from (select 'A' foo union all select 'Z' union all select null) z
where foo <!> any(array[null, 'A']);  
 카운트
-------
     삼
(1 행)

답변

운영자

이것은 @Daniel의 영리한 연산자를 기반으로 합니다.
그 동안 다형성 유형을 사용하여 함수 / 연산자 콤보를 만듭니다 . 그런 다음 구문과 마찬가지로 모든 유형에서 작동합니다 .
그리고 기능을 만드십시오 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
);

symbolhound를 사용한 빠른 검색이 비어있어 작업자 <!>가 어떤 모듈에서도 사용하지 않는 것 같습니다.

만약 이 연산자를 많이 사용하려고, 당신은 (쿼리 계획을 돕기 위해 좀 더 그것을 구체화 할 수 losthorse 의견에 제안처럼 ). 우선, 쿼리 최적화 프로그램을 지원하기 위해 COMMUTATORand NEGATOR절을 추가 할 수 있습니다 . CREATE OPERATOR위에서 다음과 같이 교체하십시오 .

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

그리고 추가하십시오 :

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

그러나 추가 조항은 사용 사례에 도움이되지 않으며 일반 색인은 여전히 ​​사용되지 않습니다. 그것을 달성하는 것이 훨씬 더 정교합니다. (시도하지 않았습니다.) 자세한 내용은 설명서의 “운영자 최적화 정보” 장을 읽으십시오 .

테스트 사례

문제의 테스트 사례는 배열의 모든 값이 동일한 경우에만 성공할 수 있습니다. 질문 ( '{null,A}'::text[]) 의 배열 의 경우 결과는 항상 참입니다. 그게 의도입니까? “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

표준 연산자로 대체

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

거의 할 수 있습니다 로 번역 될 있다

foo = ALL (test_arr) IS NOT TRUE

foo = ALL (test_arr) 수확량 …

TRUE .. 모든 요소가 있다면 foo
FALSE어떤 경우 .. NOT NULL원소<> foo
NULL .. 하나 이상의 요소가 IS NULL있고 요소가없는 경우<> foo

그래서, 나머지 코너 케이스입니다
– foo IS NULL
– 및 test_arr 아무것도하지만 구성 NULL요소.

둘 중 하나라도 배제 할 수 있다면 우리는 끝난 것입니다. 따라서
열이 정의 된 경우 단순 테스트를 사용하십시오 NOT NULL.
– 또는 당신 알고 배열이 모두 NULL을 결코 없습니다.

그렇지 않으면 추가로 테스트하십시오.

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

어디 'A'와 'B'될 수 있는 고유 한 값을. SO에 대한이 관련 질문에 대한 설명 및 대안 :
PostgreSQL에서 배열이 모두 NULL입니까?

빈 문자열 과 같이에 존재할 수없는 값을 알고있는 경우 에도 단순화 할 수 있습니다.test_arr''

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

다음은 모든 조합을 확인하기위한 완벽한 테스트 매트릭스입니다.

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

이것은 Andriy의 EXCEPT솔루션 보다 조금 더 장황 하지만 실질적으로 더 빠릅니다.

답변