Почему UNIQUE допускает несколько NULL в PostgreSQL!

Ограничение UNIQUE гарантирует уникальность значений, но для NULL в PostgreSQL действует отдельная семантика. По умолчанию два NULL считаются различными при проверке уникальности, поэтому ограничение не запрещает несколько строк с неопределённым значением:
CREATE TABLE users (
id BIGINT PRIMARY KEY,
email TEXT UNIQUE
);

Повторное определённое значение email нарушит ограничение уникальности.
INSERT INTO users (id, email)
VALUES (1, 'user@example.com');

INSERT INTO users (id, email)
VALUES (2, 'user@example.com');

При использовании NULL те же правила не применяются: несколько строк успешно проходят проверку ограничения.
INSERT INTO users (id, email)
VALUES
(3, NULL),
(4, NULL),
(5, NULL);

Особенно важно учитывать это поведение в составных ключах, где один из столбцов может быть неопределённым:
CREATE TABLE contacts (
id BIGINT PRIMARY KEY,
company_id BIGINT NOT NULL,
external_id TEXT,
UNIQUE (company_id, external_id)
);

Одинаковые определённые значения (company_id, external_id) конфликтуют, но несколько комбинаций (10, NULL) по умолчанию допустимы:
INSERT INTO contacts (id, company_id, external_id)
VALUES
(1, 10, NULL),
(2, 10, NULL);

Начиная с PostgreSQL 15 это поведение можно изменить через NULLS NOT DISTINCT. В таком ограничении NULL считаются неразличимыми при проверке уникальности:
CREATE TABLE contacts_strict (
id BIGINT PRIMARY KEY,
company_id BIGINT NOT NULL,
external_id TEXT,
UNIQUE NULLS NOT DISTINCT (company_id, external_id)
);

Теперь сохранить две строки с одной комбинацией (10, NULL) нельзя: вторая вставка завершится нарушением уникальности.
INSERT INTO contacts_strict (id, company_id, external_id)
VALUES (1, 10, NULL);

INSERT INTO contacts_strict (id, company_id, external_id)
VALUES (2, 10, NULL);

Та же семантика поддерживается уникальными индексами, поэтому правило можно выразить непосредственно на уровне индекса.
CREATE UNIQUE INDEX uq_contacts_company_external
ON contacts (company_id, external_id)
NULLS NOT DISTINCT;

NULLS NOT DISTINCT не эквивалентен NOT NULL. NOT NULL полностью запрещает отсутствие значения, тогда как NULLS NOT DISTINCT разрешает NULL, но включает его в контроль уникальности.
CREATE TABLE identifiers (
id BIGINT PRIMARY KEY,
external_id TEXT,
UNIQUE NULLS NOT DISTINCT (external_id)
);

В такой таблице может существовать одна строка с external_id IS NULL, но вторая аналогичная строка уже нарушит ограничение.

🔥 UNIQUE в PostgreSQL по умолчанию допускает несколько NULL. Если отсутствие значения также должно быть уникальным состоянием, начиная с PostgreSQL 15 это можно явно зафиксировать через NULLS NOT DISTINCT.

➡️ SQL Ready | #практика