[PostgreSQL] NULLS NOT DISTINCT

UNIQUE Index는 유일성을 보장할 수 있게 해주는 강력한 기능이지만 구멍도 있다.

테이블과 유니크를 하나 만들어보겠다.

CREATE TABLE testable (
    id SERIAL PRIMARY KEY,
    age int NULL,
    name TEXT NOT NULL
);

CREATE UNIQUE INDEX uidx_name_age on testable(name, age);

그러면 당연히 중복 데이터 조합에 대해서는 오류를 발생시킬 것이다.

INSERT INTO testable(age, name)
VALUES (10, 'john');

INSERT INTO testable(age, name)
VALUES (10, 'john');

근데 문제는, 유니크 조건 중에 null이 포함될 경우다. null이 포함되면 그건 유니크 조건에서 배제된다!

INSERT INTO testable(age, name)
VALUES (NULL, 'john');

INSERT INTO testable(age, name)
VALUES (NULL, 'john');

의도치 않은 중복 데이터가 발생할 수 있는 것이다.

NULLS NOT DISTINCT 옵션

다행히도 이 문제를 해소하기 위한 옵션이 있다.
이 기능은 PostgreSQL 15 버전부터 지원된다.

DROP INDEX uidx_name_age;

CREATE UNIQUE INDEX uidx_name_age on testable(name, age) NULLS NOT DISTINCT;

위와 같이 인덱스 생성시에 옵션을 주면, null도 하나의 값으로 간주해서 인덱스 조건으로 사용하게 된다.

그러면 이제는 중복 배제가 잘 될 것이다.

참조
https://www.postgresql.org/about/featurematrix/detail/unique-nulls-not-distinct/
https://stackoverflow.com/questions/8289100/create-unique-constraint-with-null-columns