Увійти Реєстрація
Вакансії
Вакансії Laravel Компанії Статистика
Підготовка
Як підготуватися Співбесіди Тестування Документація
Блог
Всі статті Серії
Екосистема
Пакети Ресурси Проєкти Інструменти Події Відео
Інше
Про нас Реклама

Як 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

Схожі питання