본문 바로가기
빌드노트 검색

빌드노트

PostgreSQL UNIQUE인데 NULL이 든 같은 키가 저장되는 이유

nullable 복합 키의 기본 UNIQUE는 NULL을 포함한 중복을 허용한다. PostgreSQL 18.6의 실제 INSERT와 23505를 비교하고, 15에서 도입된 NULLS NOT DISTINCT의 선택 기준과 기존 중복을 정리해야 하는 제약 변경 경계를 설명한다.

PostgreSQL에서 UNIQUE (tenant_id, external_code)를 걸었는데 (1, NULL)이 두 행 저장될 수 있다. 기본 UNIQUE는 NULL끼리 같은 값으로 취급하지 않기 때문이다. 이 키에서 NULL을 어떻게 볼지 먼저 정해야 한다. 서로 다른 미정 값으로 허용할 수도 있고, “이 테넌트의 코드 미지정 항목은 하나”라고 제한할 수도 있다. 뒤의 규칙이라면 UNIQUE NULLS NOT DISTINCT를 검토한다. 고유 제약의 NULL 처리 규칙 (새 탭에서 열림)

이 문법은 PostgreSQL 15에서 도입됐다. 서버에 SHOW server_version;을 실행해 확인한다. 클라이언트인 psql 버전만 확인해서는 서버의 지원 여부를 알 수 없다. 아래 비교는 새 실습 컨테이너의 PostgreSQL 18.6에서 실행한 합성 예제다. PostgreSQL 15 도입 기록 (새 탭에서 열림)

같은 테넌트의 NULL 두 행이 들어간다

별도 실습 DB에 연결한 psql 세션에서 다음을 실행한다. 임시 테이블은 연결이 끝나면 사라진다. 두 열을 모두 nullable로 두어 NULL 위치도 비교할 수 있게 했다. 실제 업무에서 테넌트가 필수라면 그 열에는 별도로 NOT NULL이 필요하다.

앞의 설정은 뒤에서 의도한 오류가 나도 다음 문장을 실행하고, 오류 코드와 NULL 표시를 읽기 위한 것이다. psql의 자동 커밋 상태에서 실행하며 BEGIN으로 감싸지 않는다. psql 설정 (새 탭에서 열림)

\set ON_ERROR_STOP off
\set VERBOSITY verbose
\pset format unaligned
\pset null '<NULL>'
SHOW server_version;

CREATE TEMP TABLE unique_null_demo (
  tenant_id integer,
  external_code text,
  CONSTRAINT uq_demo_default UNIQUE (tenant_id, external_code)
);
INSERT INTO unique_null_demo VALUES (1, NULL), (1, NULL);

INSERT는 INSERT 0 2로 성공했다. NULL을 빈칸으로 표시하는 편집기에서는 두 행의 키를 알아보기 어려울 수 있다. 다음 쿼리는 키별로 묶어 중복 후보를 찾는다. NULL 행도 집계하려고 count(*)를 사용한다. count(external_code)로 바꾸면 이 그룹의 개수가 0으로 나온다. count가 세는 대상 (새 탭에서 열림)

SELECT tenant_id, external_code, count(*) AS rows
FROM unique_null_demo
GROUP BY tenant_id, external_code
HAVING count(*) > 1;

실제 출력은 다음과 같았다. <NULL>은 출력용 표시이며 저장된 문자열이 아니다.

tenant_id|external_code|rows
1|<NULL>|2
(1 row)

이 두 행을 업무상 중복으로 볼지는 별도 판단이다. 외부 코드가 아직 도착하지 않은 서로 다른 두 항목이라면 NULL을 포함한 키만으로 둘을 합칠 수 없다. 반대로 테넌트마다 코드 없는 기본 항목을 하나만 두는 규칙이라면 지금의 제약은 그 규칙보다 느슨하다.

NULL도 같은 키로 보면 두 번째 INSERT가 막힌다

같은 세션에서 다른 임시 테이블을 만든다. 고유 제약에 NULLS NOT DISTINCT를 지정한 점이 다르다.

CREATE TEMP TABLE unique_null_same_demo (
  tenant_id integer,
  external_code text,
  CONSTRAINT uq_demo_same UNIQUE NULLS NOT DISTINCT (tenant_id, external_code)
);
INSERT INTO unique_null_same_demo VALUES (1, NULL);
INSERT INTO unique_null_same_demo VALUES (1, NULL);

첫 INSERT는 INSERT 0 1로 성공했고 두 번째는 다음 오류로 거절됐다. 오류 출력에서 필요한 두 줄을 옮겼다. 23505는 고유 제약 위반인 unique_violation이다. PostgreSQL 오류 코드 (새 탭에서 열림)

ERROR:  23505: duplicate key value violates unique constraint "uq_demo_same"
DETAIL:  Key (tenant_id, external_code)=(1, null) already exists.

별도 비교 테이블에서는 아래 네 조합을 각각 두 번씩 넣었다. 두 제약 모두 첫 입력은 성공했다. 두 번째 입력 결과를 정리하면 다음과 같다.

  • (1, 'A'): 기본 UNIQUE와 NULLS NOT DISTINCT 모두 23505였다.
  • (1, NULL), (NULL, 'A'), (NULL, NULL): 기본 UNIQUE에서는 다시 저장됐고, NULLS NOT DISTINCT에서는 모두 23505였다.

NULL이 테이블 전체에서 한 번만 허용된다는 뜻은 아니다. (1, NULL)이 있는 상태에서 (2, NULL)은 두 제약 모두 저장됐다. (NULL, 'A') 다음의 (NULL, 'B')도 저장됐다. 두 열을 합친 키가 같은지 판단하는 규칙이므로 다른 열의 값이 다르면 구별된다.

같은 입력의 결과가 이 비교와 다르면 서버 버전과 실제 테이블의 고유 제약 정의부터 확인한다. 다른 열을 포함한 제약, 부분 고유 인덱스, NULL을 바꾸는 입력 처리까지 이 예제와 같다고 가정하지 않는다.

이미 쌓인 중복은 제약을 추가한다고 정리되지 않는다

앞서 NULL 두 행을 넣은 unique_null_demo에 새 규칙을 추가해 본다. 다음 명령도 실습용 임시 테이블에만 실행한다.

ALTER TABLE unique_null_demo
  ADD CONSTRAINT uq_demo_new
  UNIQUE NULLS NOT DISTINCT (tenant_id, external_code);

실제로는 23505: could not create unique index "uq_demo_new"로 실패했다. 기존 두 행이 새 규칙을 위반하기 때문이다. 새 제약은 만들어지지 않았고 기존 UNIQUE는 남았다. 앞의 GROUP BY 쿼리를 자기 테이블의 업무 키 열로 바꿔 실행하면 먼저 조사할 중복 그룹을 찾을 수 있다. 여기서 나온 NULL 그룹을 곧바로 삭제 대상으로 해석하면 안 된다.

제약 교체 경계는 별도 합성 테이블에서도 확인했다. 기존 제약을 내리고 새 제약을 추가하는 작업을 하나의 ALTER TABLE 문으로 실행했을 때, 중복 때문에 실패하자 기존 제약도 그대로 남았다. 합성 자료에서 제거할 행 ID를 명시해 중복 한 행을 없앤 뒤에는 교체가 성공했다. 이후 (1, NULL) 재입력은 거절됐고 (2, NULL)은 저장됐다. 어떤 업무 행을 남길지는 이 실험이 정해 주지 않는다.

운영 변경에서는 중복 조회와 정리 사이에도 쓰기가 계속될 수 있다. 조회 결과가 0행이라는 사실만으로 변경 준비가 끝난 것은 아니다. 정리 기준과 새 중복 유입을 막을 쓰기 제어, 기존 제약의 참조 관계를 확인한 뒤 교체 절차를 정한다. 일반적인 ADD UNIQUE는 잠금이 필요한 작업이고 NOT VALID로 기존 중복 검사를 미룰 수 없다. 이번 실습에서도 UNIQUE에 NOT VALID를 붙인 명령은 0A000으로 거절됐다. ALTER TABLE의 제약 추가 조건과 잠금 (새 탭에서 열림)

선택은 NULL의 의미에 달려 있다. 미정 상태인 서로 다른 항목을 계속 받아야 한다면 기본 UNIQUE를 유지할 수 있다. NULL을 같은 업무 키로 묶어 하나만 허용하려면 지원 서버에서 NULLS NOT DISTINCT를 검토한다. 키가 반드시 확정돼야 한다면 NOT NULL로 누락 자체를 막는 쪽을 검토한다. NULL을 빈 문자열이나 -1 같은 상수로 무작정 치환하면 실제 값과 충돌하거나 “미정”이라는 의미를 잃을 수 있다.

이 실험은 작은 일반 테이블의 저장 성공·거절과 기존 중복의 제약 교체 경계를 확인했다. PostgreSQL 15 자체의 실행과 14 이하의 대안, 운영 데이터 정리, 동시 쓰기 중의 교체 시간·성능은 검증하지 않았다. 조회의 NOT IN에서 NULL을 다루는 문제와 달리, 여기서 결정할 것은 저장할 때 어떤 두 행을 같은 업무 키로 볼 것인가다.

← 전체 글 보기