PostgreSQL 유니크 인덱스 NULL 처리

운영 중 PostgreSQL 14버전에서 유니크 인덱스에서 NULL 의 중복 입력이 가능한 것을 확인하였습니다.

 

CREATE UNIQUE INDEX UNIQUE_INDEX ON TABLEA (COLA, COLB)

위와 같은 인덱스가 생성되어 있는 상태에서

아래 데이터가 여러건 조회가 발생합니다. 

SELECT COUNT(*) FROM TABLEA  WHERE COLA = '' AND COLB IS NULL;

 

CREATE UNIQUE INDEX UNIQUE_INDEX_01 ON TABLEA (COLA, COLB) WHERE COLB IS NOT NULL;
CREATE UNIQUE INDEX UNIQUE_INDEX_02 ON TABLEA  (COLA) WHERE COLB IS NULL;

유니크 인덱스 2개를 통하여 해당 상황은 방지할 수 있었는데

14버전 이후에 해당 기능이 변경이 되었을지 나중에 버전 업그레이드에 따른 영향도가 있을지가 걱정되어 

좀 더 확인을 진행하였습니다.

 

15버전 부터는 UNIQUE NULLS NOT DISTINCT 옵션을 지정할 수 있습니다.

CREATE UNIQUE INDEX UNIQUE_INDEX ON TABLEA  (COLA , COLB) NULLS NOT DISTINCT

이러한 기능이 추가된 것을 보아 PostgreSQL 에서는 다른 DBMS 와 유니크에 대한 NULL 접근 방식이 다른 것으로 이해하였고 위 인덱스를 추가하여 배포 진행하였습니다.

 

https://pganalyze.com/blog/5mins-postgres-unique-constraint-null-parallel-distinct

https://www.enterprisedb.com/postgres-tutorials/postgresql-unique-constraint-null-allowing-only-one-null

https://stackoverflow.com/questions/8289100/create-unique-constraint-with-null-columns