PostgreSQL에서 NULL로 바뀐 행을 <>가 놓치는 이유

PostgreSQL에서 이전 값과 새 값을 old_note <> new_note로 비교하면, 'draft'에서 NULL로 바뀐 행이 조회되지 않는다. 반대로 NULL에 새 값이 채워진 행도 빠진다. 일반 비교는 한쪽이라도 NULL이면 참이나 거짓 대신 UNKNOWN을 반환하고, WHERE는 참인 행만 남기기 때문이다. NULL로 비우거나 채운 것도 변경으로 찾아야 한다면 old_note IS DISTINCT FROM new_note를 쓴다. 일반 비교와 NULL 비교의 공식 규칙↗ (새 탭에서 열림)

이 선택은 양쪽이 모두 NULL일 때는 변경이 없다고 보는 규칙이다. NULL을 빈 문자열이나 0으로 바꾼 뒤 비교하는 방법과도 다르다. 치환값이 실제 데이터로 들어올 수 있으면 서로 다른 두 상태가 같은 값으로 합쳐진다.

다섯 행에서 비교식과 필터 결과를 함께 본다

아래는 실제 장애 기록이 아니라, 별도 PostgreSQL 18.6 인스턴스에서 합성 자료로 실행한 결과다. 따라 하려면 실습용 PostgreSQL DB에 연결한 psql의 새 세션에서 시작하고, 뒤의 SQL까지 같은 연결을 유지한다. TEMP 테이블은 연결이 끝나면 사라진다. 임시 테이블의 수명↗ (새 탭에서 열림)

old_note와 new_note는 한 항목의 이전 값과 새 값이다. 같은 값, 다른 값, NULL로 비우기, NULL에 값 채우기, 양쪽 NULL을 한 행씩 넣는다. 앞의 두 psql 설정은 출력 구분자와 NULL 표시만 바꾼다. [NULL]이라는 문자열을 데이터에 넣는 명령이 아니다. psql 출력 설정↗ (새 탭에서 열림)

\pset format unaligned
\pset null '[NULL]'
CREATE TEMP TABLE note_changes (
  id integer PRIMARY KEY,
  old_note text,
  new_note text
);
INSERT INTO note_changes VALUES
  (1, 'draft', 'draft'),
  (2, 'draft', 'ready'),
  (3, 'draft', NULL),
  (4, NULL, 'ready'),
  (5, NULL, NULL);

SELECT id, old_note, new_note,
       old_note <> new_note AS by_neq,
       old_note IS DISTINCT FROM new_note AS by_distinct
FROM note_changes
ORDER BY id;

마지막 SELECT의 실제 출력이다. t는 참, f는 거짓이다. 비교 결과 열의 [NULL]은 UNKNOWN에 해당한다.

id|old_note|new_note|by_neq|by_distinct
1|draft|draft|f|f
2|draft|ready|t|t
3|draft|[NULL]|[NULL]|t
4|[NULL]|ready|[NULL]|t
5|[NULL]|[NULL]|[NULL]|f
(5 rows)

두 값이 모두 있는 1·2번에서는 두 비교가 같다. 차이는 3·4번이다. 비우기와 채우기를 <>는 참으로 판정하지 못하지만, IS DISTINCT FROM은 둘 다 참으로 판정한다. 양쪽 NULL인 5번에서는 IS DISTINCT FROM이 거짓을 반환한다. 이때의 거짓은 이 비교 술어가 정한 결과이며, 일반 비교의 NULL = NULL이 참이라는 뜻은 아니다.

이제 같은 두 비교식을 각각 WHERE에 넣어 실제로 어떤 행이 남는지 확인한다.

SELECT id FROM note_changes
WHERE old_note <> new_note
ORDER BY id;

SELECT id FROM note_changes
WHERE old_note IS DISTINCT FROM new_note
ORDER BY id;

첫 조회는 id=2 한 행만 반환했다. 두 번째 조회는 id=2, 3, 4 세 행을 반환했다. 첫 조회에서 3·4번이 빠진 것은 “같다”는 판정을 받았기 때문이 아니다. 조건이 UNKNOWN이었기 때문이다. WHERE의 공식 설명↗ (새 탭에서 열림)대로 거짓과 NULL 결과는 모두 필터에서 제외된다.

자기 조회에서도 빠지는 행의 두 입력과 비교식을 WHERE 적용 전에 함께 확인한다. 입력에는 NULL이 있는데 결과를 빈칸으로 표시하고 있었다면 먼저 NULL 표시를 구분한다. NOT (old_note = new_note)로 바꾸는 것만으로는 해결되지 않는다. 같은 다섯 행에서 이 식도 3·4·5번에 UNKNOWN을 반환했다. 입력이 모두 비NULL인 행만 빠진다면 이 재현으로는 원인을 설명할 수 없으므로 다른 필터와 입력식을 확인한다.

COALESCE로 채우면 빈 문자열로 바뀐 사실이 사라진다

NULL을 ''로 바꾸면 비교 결과에 UNKNOWN이 없어져 해결된 것처럼 보인다. 하지만 “값이 없음”과 “길이가 0인 문자열”은 여전히 다른 입력일 수 있다. 앞의 테이블에 그 두 상태 사이의 전환을 추가해 비교한다.

INSERT INTO note_changes VALUES (6, NULL, ''), (7, '', NULL);
SELECT id,
       COALESCE(old_note, '') <> COALESCE(new_note, '') AS by_coalesce,
       old_note IS DISTINCT FROM new_note AS by_distinct
FROM note_changes
WHERE id IN (6, 7)
ORDER BY id;

실제 출력에서 치환 후 비교는 두 전환을 모두 놓쳤다.

id|by_coalesce|by_distinct
6|f|t
7|f|t
(2 rows)

COALESCE는 인자 중 처음으로 NULL이 아닌 값을 반환한다. 따라서 이 식에서는 NULL도 빈 문자열도 ''가 된다. 그다음의 <>는 같은 두 빈 문자열을 비교하므로 거짓을 반환한다. NULL을 다루는 비교를 한 것이 아니라, 비교할 입력의 의미를 먼저 바꾼 것이다. COALESCE의 공식 동작↗ (새 탭에서 열림)

추가한 행까지 포함해 WHERE 조건도 비교했다. WHERE COALESCE(old_note, '') <> COALESCE(new_note, '')는 2·3·4번만 남겼고, WHERE old_note IS DISTINCT FROM new_note는 2·3·4·6·7번을 남겼다. 치환 방식은 앞의 'draft'와 NULL 비교에서는 차이를 찾았지만, 실제 값이 치환값과 같아지는 순간 변경을 숨겼다.

숫자도 같은 문제를 갖는다. 다음 입력은 값 없음에서 실제 0으로 바뀌는 경우를 확인한다.

SELECT NULL::integer IS DISTINCT FROM 0 AS by_distinct,
       COALESCE(NULL::integer, 0) <> COALESCE(0, 0) AS by_coalesce;

실제 결과는 by_distinct=t, by_coalesce=f였다. 별도 정수 입력 비교에서 0 → NULL도 같은 결과였다. 반면 NULL → 7과 7 → NULL은 두 방식 모두 참이었다. 치환 방식이 언제나 실패하는 것이 아니라, 치환값과 실제 값이 충돌하는 입력을 놓친다는 차이다.

이전 값과 새 값의 차이를 찾는 조회라면, NULL 전환을 변경에 포함할지 먼저 정한다. 포함하고 양쪽 NULL은 변경 없음으로 본다면 IS DISTINCT FROM을 선택한다. 빈 문자열이나 0을 값 없음과 같은 상태로 보려는 명시적인 업무 규칙이 있을 때만 그에 맞는 치환을 적용한다. 단순히 UNKNOWN을 없애려고 고른 상수를 변경 감지 기준으로 삼지 않는다.

이번 실험은 단일 연결에서 text와 integer의 두 값을 비교하고 WHERE 반환 행을 확인했다. 운영 데이터, 여러 열을 묶은 비교, 사용자 정의 타입, 동시 변경이나 실행 계획·성능은 검증하지 않았다.