[SQL] NULL은 Postgres를 배열에 존재하는지 확인
SQLNULL은 Postgres를 배열에 존재하는지 확인
NULL 값은 배열에있는 경우이 질문에 유사, 어떻게 찾을 수 있습니까?
여기에 몇 가지 시도이다.
SELECT num, ar, expected,
ar @> ARRAY[NULL]::int[] AS test1,
NULL = ANY (ar) AS test2,
array_to_string(ar, ', ') <> array_to_string(ar, ', ', '(null)') AS test3
FROM (
SELECT 1 AS num, '{1,2,NULL}'::int[] AS ar, true AS expected
UNION SELECT 2, '{1,2,3}'::int[], false
) td ORDER BY num;
num | ar | expected | test1 | test2 | test3
-----+------------+----------+-------+-------+-------
1 | {1,2,NULL} | t | f | | t
2 | {1,2,3} | f | f | | f
(2 rows)
쇼에게 기대 값을 array_to_string 만 트릭. 이를 테스트 할 수있는 더 좋은 방법이 있나요?
해결법
-
==============================
1.당신이 당신의 배열에 존재하지 않을 수있는 하나의 요소를 알고 있다면, 당신은 포스트 그레스 9.1에서이 빠르게 표현 (또는 포스트 그레스의 모든 버전)를 사용할 수 있습니다. 당신이 긍정적 인 숫자의 배열을 말해봐, 그래서 -1에있을 수 없습니다
당신이 당신의 배열에 존재하지 않을 수있는 하나의 요소를 알고 있다면, 당신은 포스트 그레스 9.1에서이 빠르게 표현 (또는 포스트 그레스의 모든 버전)를 사용할 수 있습니다. 당신이 긍정적 인 숫자의 배열을 말해봐, 그래서 -1에있을 수 없습니다
-1 = ANY(ar) IS NULL자세한 설명과 관련 대답 :
당신이 절대적으로 확신 할 수없는 경우 unnest로 비싸지 만 안전한 방법 중 하나를 ()로 다시 떨어질 수있다. 처럼:
(SELECT bool_or(x IS NULL) FROM unnest(ar) x)또는:
EXISTS (SELECT 1 FROM unnest(ar) x WHERE x IS NULL)하지만 당신은 빠르고 안전한 CASE 식 수 있습니다. 있을 법하지 않는 번호를 사용하고 존재하는 경우는 안전한 방법으로 폴백. 별도로 IS NULL 아칸소 사건을 처리 할 수 있습니다. 데모 아래를 참조하십시오.
포스트 그레스 9.3에서 또는 나중에는 내장의 기능 array_remove () 또는 array_replace으로 테스트 할 수 있습니다 ().
또는 @Patrick 포스트 그레스 9.5 이상과 같은 사용 array_position은 () 추가. 나는 향상된 변종을 포함.
SELECT num, ar, expect , -1 = ANY(ar) IS NULL AS t_1 -- 50 ms , (SELECT bool_or(x IS NULL) FROM unnest(ar) x) AS t_2 -- 754 ms , EXISTS (SELECT 1 FROM unnest(ar) x WHERE x IS NULL) AS t_3 -- 521 ms , CASE -1 = ANY(ar) WHEN FALSE THEN FALSE WHEN TRUE THEN EXISTS (SELECT 1 FROM unnest(ar) x WHERE x IS NULL) ELSE NULLIF(ar IS NOT NULL, FALSE) -- catch ar IS NULL -- 55 ms -- ELSE TRUE -- simpler for columns defined NOT NULL -- 51 ms END AS t_91 , array_replace(ar, NULL, 0) <> ar AS t_93a -- 99 ms , array_remove(ar, NULL) <> ar AS t_93b -- 96 ms , cardinality(array_remove(ar, NULL)) <> cardinality(ar) AS t_94 -- 81 ms , COALESCE(array_position(ar, NULL::int), 0) > 0 AS t_95a -- 49 ms , array_position(ar, NULL) IS NOT NULL AS t_95b -- 45 ms , CASE WHEN ar IS NOT NULL THEN array_position(ar, NULL) IS NOT NULL END AS t_95c -- 48 ms FROM ( VALUES (1, '{1,2,NULL}'::int[], true) -- extended test case , (2, '{-1,NULL,2}' , true) , (3, '{NULL}' , true) , (4, '{1,2,3}' , false) , (5, '{-1,2,3}' , false) , (6, NULL , null) ) t(num, ar, expect);결과:
num | ar | expect | t_1 | t_2 | t_3 | t_91 | t_93a | t_93b | t_94 | t_95a | t_95b | t_95c -----+-------------+--------+--------+------+-----+------+-------+-------+------+-------+-------+------- 1 | {1,2,NULL} | t | t | t | t | t | t | t | t | t | t | t 2 | {-1,NULL,2} | t | f --!! | t | t | t | t | t | t | t | t | t 3 | {NULL} | t | t | t | t | t | t | t | t | t | t | t 4 | {1,2,3} | f | f | f | f | f | f | f | f | f | f | f 5 | {-1,2,3} | f | f | f | f | f | f | f | f | f | f | f 6 | NULL | NULL | t --!! | NULL | f | NULL | NULL | NULL | NULL | f | f | NULL그 array_remove ()와 array_position ()를 참고 다차원 배열 허용되지 않습니다. 1 dimenstioal 배열 t_93a에만 작업의 오른쪽에있는 모든 표현.
dB <> 바이올린 여기 (포스트 그레스 11 개 시험 포함). 포스트 그레스 9.6에 대 한 오래 된 sqlfiddle.
추가 된 시간은 포스트 그레스 9.5에서 200K 행 벤치 마크 테스트에서입니다. 이것은 내 설정이다 :
CREATE TEMP TABLE t AS SELECT row_number() OVER() AS num , array_agg(elem) AS ar , bool_or(elem IS NULL) AS expected FROM ( SELECT CASE WHEN random() > .95 THEN NULL ELSE g END AS elem -- 5% NULL VALUES , count(*) FILTER (WHERE random() > .8) OVER (ORDER BY g) AS grp -- avg 5 element per array FROM generate_series (1, 1000000) g -- increase for big test case ) sub GROUP BY grp;반복 사용을 위해이 같은 포스트 그레스 9.5에서 함수를 만들 것입니다 :
CREATE OR REPLACE FUNCTION f_array_has_null (anyarray) RETURNS bool LANGUAGE sql IMMUTABLE AS 'SELECT array_position($1, NULL) IS NOT NULL';다형성 입력을 사용하는 것이 아니라 INT 모든 어레이 형이 작동 입력 [].
불변 성능 최적화 및 인덱스 표현을 할 수 있도록합니다.
그러나 "함수가 인라인"손상 시키 성능을 사용하지 것이다, 그것은 엄격한하지 않습니다.
대신 기능 엄격한 사용을 만드는, IS NULL AR 사건을 잡을해야하는 경우 :
CREATE OR REPLACE FUNCTION f_array_has_null (anyarray) RETURNS bool LANGUAGE sql IMMUTABLE AS 'SELECT CASE WHEN $1 IS NOT NULL THEN array_position($1, NULL) IS NOT NULL END';포스트 그레스 9.1 이상에서 t_91 식을 사용합니다. 나머지는 변경 적용됩니다.
밀접하게 관련:
-
==============================
2.PostgreSQL의 9.5 (난 당신이 9.1 spcified 알고 있지만, 어쨌든)이 사소한으로 뭔가 (테스트 4 참조))합니다 (끔찍하게 비효율적 unnest를 사용하지 않고도 당신이 원하는 것을 할 수있는 array_position () 함수가 있습니다 :
PostgreSQL의 9.5 (난 당신이 9.1 spcified 알고 있지만, 어쨌든)이 사소한으로 뭔가 (테스트 4 참조))합니다 (끔찍하게 비효율적 unnest를 사용하지 않고도 당신이 원하는 것을 할 수있는 array_position () 함수가 있습니다 :
patrick@puny:~$ psql -d test psql (9.5.0) Type "help" for help. test=# SELECT num, ar, expected, ar @> ARRAY[NULL]::int[] AS test1, NULL = ANY (ar) AS test2, array_to_string(ar, ', ') <> array_to_string(ar, ', ', '(null)') AS test3, coalesce(array_position(ar, NULL::int), 0) > 0 AS test4 FROM ( SELECT 1 AS num, '{1,2,NULL}'::int[] AS ar, true AS expected UNION SELECT 2, '{1,2,3}'::int[], false ) td ORDER BY num; num | ar | expected | test1 | test2 | test3 | test4 -----+------------+----------+-------+-------+-------+------- 1 | {1,2,NULL} | t | f | | t | t 2 | {1,2,3} | f | f | | f | f (2 rows) -
==============================
3.PostgreSQL의 UNNEST () 함수를 더 choice.You 배열에 NULL 값을 확인하기 위해 아래와 같은 간단한 함수를 작성할 수있다.
PostgreSQL의 UNNEST () 함수를 더 choice.You 배열에 NULL 값을 확인하기 위해 아래와 같은 간단한 함수를 작성할 수있다.
create or replace function NULL_EXISTS(val anyelement) returns boolean as $$ select exists ( select 1 from unnest(val) arr(el) where el is null ); $$ language sql예를 들어,
SELECT NULL_EXISTS(array [1,2,NULL]) ,NULL_EXISTS(array [1,2,3]);결과:
null_exists null_exists ----------- -------------- t f그래서, 당신은 아래와 같이 쿼리에 NULL_EXISTS () 함수를 사용할 수 있습니다.
SELECT num, ar, expected,NULL_EXISTS(ar) FROM ( SELECT 1 AS num, '{1,2,NULL}'::int[] AS ar, true AS expected UNION SELECT 2, '{1,2,3}'::int[], false ) td ORDER BY num; -
==============================
4.나는 이것을 사용
나는 이것을 사용
select array_position(array[1,null], null) is not null
from https://stackoverflow.com/questions/34848009/check-if-null-exists-in-postgres-array by cc-by-sa and MIT license
'SQL' 카테고리의 다른 글
| [SQL] 표 트리거 / 함수 (2.5 이하로 떨어지는 평균 등급을 정지)를 볼 수있다 돌연변이 (0) | 2020.06.16 |
|---|---|
| [SQL] 읽기 / MySQL의에서 쓰기 유니 코드 데이터를 (0) | 2020.06.16 |
| [SQL] IN 절 안에 CASE 문을 사용하여 (0) | 2020.06.16 |
| [SQL] 왜 RAND ()는 난수를 생성되지 않는 이유는 무엇입니까? (0) | 2020.06.16 |
| [SQL] SQL Server의 합계 시간 [중복] (0) | 2020.06.16 |