PostgreSQL SUM이 NULL일 때, 0으로 바꾸기 전에 확인할 것

PostgreSQL에서 sum(amount)로 합계를 조회했는데 숫자 대신 NULL이 나올 수 있다. 조건에 맞는 행이 없거나, 행은 있어도 amount가 전부 NULL이면 그렇다. 숫자 합계 하나를 기대하는 코드라면 두 경우를 확인해야 한다.

화면에서 이 NULL을 0으로 표시하기로 했다면 coalesce(sum(amount), 0)처럼 집계 결과를 감싼다. sum(coalesce(amount, 0))로 각 값을 먼저 바꾸면 빈 입력에서는 여전히 NULL이 나온다. 다만 GROUP BY 결과 행 자체가 없다면, 집계 밖의 COALESCE도 그 행을 만들어 주지는 않는다.

이는 PostgreSQL이 정한 집계 동작이다. SUM은 NULL이 아닌 입력값을 더하고, 입력 행이 없으면 NULL을 반환한다. count(*)는 입력 행 수, count(amount)는 그중 amount가 NULL이 아닌 행 수다. 두 개수를 같이 보면 같은 NULL 합계의 원인을 구별할 수 있다. PostgreSQL 18 집계 함수 문서↗ (새 탭에서 열림)

행이 없는 경우와 값이 없는 경우를 나눠 본다

아래는 실제 업무 데이터가 아닌 합성 정수로 실행한 실험이다. PostgreSQL 18.6 전용 컨테이너에서 확인했다. 직접 확인하려면 PostgreSQL과 psql, 임시 테이블을 만들 수 있는 테스트 DB 연결이 필요하다. psql로 접속한 뒤 아래 입력을 같은 세션에서 실행한다. \pset은 NULL을 빈칸과 구별하고 출력을 간단히 보이게 하는 psql 명령이다.

\pset format unaligned
\pset fieldsep '|'
\pset null NULL
CREATE TEMP TABLE sum_probe (bucket text, amount integer);
INSERT INTO sum_probe VALUES
  ('nulls', NULL), ('nulls', NULL),
  ('zero', 0),
  ('mixed', NULL), ('mixed', 0), ('mixed', 8), ('mixed', -3),
  ('balanced', NULL), ('balanced', 0), ('balanced', 5), ('balanced', -5);

empty라는 값은 넣지 않았다. 따라서 다음 조건은 테이블 전체를 비우지 않고도 입력 행이 없는 상황을 만든다.

SELECT count(*) AS n_rows, count(amount) AS n_values,
       sum(amount) AS total,
       sum(coalesce(amount, 0)) AS inner_zero,
       coalesce(sum(amount), 0) AS outer_zero
FROM sum_probe WHERE bucket = 'empty';

실제 출력은 아래와 같았다. 입력은 0행이지만, 이 집계 조회의 결과는 1행이다.

n_rows|n_values|total|inner_zero|outer_zero
0|0|NULL|NULL|0
(1 row)

같은 SELECT의 조건을 nulls, zero, mixed, balanced로 바꿔 실행했다. 각 조회는 1행을 반환했다. 조건별 관찰값을 모으면 다음과 같다.

조건      n_rows  n_values  total  inner_zero  outer_zero
empty     0       0         NULL   NULL        0
nulls     2       0         NULL   0           0
zero      1       1         0      0           0
mixed     4       3         5      5           5
balanced  4       3         0      0           0

empty와 nulls는 total만 보면 구별되지 않는다. 하지만 empty는 읽은 행부터 없고, nulls는 두 행을 읽었어도 합산할 값이 없다. 자기 조회를 진단할 때는 count(*)와 count(amount)를 실제 합계와 같은 FROM·WHERE 조건으로 조회한다. 조인이 있다면 두 개수도 조인 결과를 기준으로 한다.

숫자 0도 별도로 읽어야 한다. zero는 실제 값 0이 있는 경우이고, balanced는 5와 -5가 상쇄된 경우다. 둘 다 합계는 0이다. 합계가 0이라는 이유로 입력이 없거나 모든 값이 0이었다고 판단할 수는 없다. mixed에서는 NULL 한 개를 제외한 0, 8, -3을 더해 5가 나왔다.

inner_zero와 outer_zero가 갈라지는 곳은 빈 입력이다. 집계 안의 coalesce(amount, 0)는 존재하는 행의 NULL을 바꾼다. nulls의 두 행은 각각 0으로 바뀌지만, empty에는 바꿀 행이 없다. 집계 밖의 식은 SUM이 반환한 NULL을 0으로 바꾼다. COALESCE가 왼쪽부터 첫 번째 NULL 아닌 인자를 선택한다는 문서 설명과도 맞는다. COALESCE 문서↗ (새 탭에서 열림)

GROUP BY에서 빠진 행은 COALESCE로 생기지 않는다

같은 테이블과 빈 조건에 GROUP BY bucket을 붙이면 결과 모양이 달라진다.

SELECT bucket, coalesce(sum(amount), 0) AS total
FROM sum_probe WHERE bucket = 'empty'
GROUP BY bucket;

bucket|total
(0 rows)

앞에서는 0을 담은 결과 행이 하나 있었지만, 이번에는 empty 그룹 자체가 없다. COALESCE는 반환할 행 안의 값을 대체할 뿐, 없는 그룹을 추가하지 않는다. 반면 조건을 nulls로 바꾸면 nulls|0 한 행이 나온다. 값이 전부 NULL이어도 두 입력 행으로 그룹은 만들어지기 때문이다.

전체 테이블을 bucket별로 묶은 실행에서도 결과는 balanced, mixed, nulls, zero의 4행이었다. empty는 생기지 않았다. 문서도 같은 그룹 키를 가진 행들을 하나의 그룹 행으로 묶는다고 설명한다. 여기서 빈 입력에 결과 1행이 나온 것은 GROUP BY와 HAVING이 없는 단일 집계 조회의 동작이다. HAVING으로 결과를 거르는 조회까지 1행을 보장하는 것은 아니다. GROUP BY와 HAVING 문서↗ (새 탭에서 열림)

따라서 합계가 비었다면 먼저 반환값이 NULL인지, 조회 결과가 0행인지 확인한다. 특정 항목의 합계 하나가 필요하면 앞의 단일 집계 형태에서 coalesce(sum(amount), 0)를 쓸 수 있다. 그룹별 목록에 데이터가 없는 항목도 보여야 한다면, 표시할 항목 목록을 기준으로 집계 결과를 연결하도록 조회 구조를 정해야 한다. COALESCE 위치만 바꿔서는 그 목록을 채울 수 없다.

0으로 표시하는 선택은 데이터의 의미와도 맞아야 한다. NULL이 ‘아직 값이 들어오지 않음’을 뜻한다면 이를 0으로 바꾸면서 실제 계산 결과 0과 구분할 정보가 사라진다. 그런 구분이 필요할 때는 원래 합계와 두 개수를 함께 유지하고, 표시할 값에만 기본값을 적용한다.

확인 후에는 같은 세션에서 DROP TABLE sum_probe;를 실행하면 된다. 임시 테이블은 세션을 닫아도 사라진다. 임시 테이블 문서↗ (새 탭에서 열림)

이번 관찰은 PostgreSQL 18.6의 정수 열과 위 조건에 한정한다. 애플리케이션 드라이버가 NULL이나 0행을 어떤 타입·객체로 전달하는지, 다른 DB에서도 같은 결과가 나는지는 검증하지 않았다.