Як PostgreSQL порівнює NULL в унікальних обмеженнях і що змінив NULLS NOT DISTINCT?

За стандартом SQL NULL означає «невідоме», а невідоме не дорівнює іншому невідомому. Тому унікальне обмеження дозволяє скільки завгодно NULL:

CREATE TABLE users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    phone text UNIQUE
);

INSERT INTO users (phone) VALUES (NULL), (NULL);   -- обидва вставлено

Для необов'язкових унікальних полів (телефон, нікнейм) це якраз потрібна поведінка: багато користувачів без телефону, але два однакові телефони - заборонено.

Де це підводить - складені ключі з необов'язковою частиною:

CREATE TABLE settings (
    user_id bigint NOT NULL,
    team_id bigint,          -- NULL = налаштування на рівні користувача
    key text NOT NULL,
    value text,
    UNIQUE (user_id, team_id, key)
);

INSERT INTO settings VALUES (1, NULL, 'theme', 'dark'), (1, NULL, 'theme', 'light');
-- обидва вставлено: дублікат, бо NULL ≠ NULL

NULLS NOT DISTINCT (PostgreSQL 15+) - вважати NULL однаковими:

UNIQUE NULLS NOT DISTINCT (user_id, team_id, key)

Тепер друга вставка порушить унікальність.

До PostgreSQL 15 обходили так:

• два часткові унікальні індекси - WHERE team_id IS NULL і WHERE team_id IS NOT NULL;
• індекс за виразом COALESCE(team_id, 0) - працює, але «магічне» значення легко пропустити.

Пов'язане - ON CONFLICT: upsert спирається на унікальне обмеження. Без NULLS NOT DISTINCT рядки з NULL у ключі ніколи не конфліктують, і upsert замість оновлення вставлятиме дублікати.

MySQL поводиться так само, як PostgreSQL за замовчуванням (кілька NULL дозволено), а налаштування на зразок NULLS NOT DISTINCT не має.

Докладніше в документації: CREATE TABLE