UNIQUE разрешил второй пустой ключ: NULLS NOT DISTINCT

Справочник хранит настройки организации: organization_id, scope и значение. NULL в scope означает общую настройку. UNIQUE по двум полям защищает именованные области, но позволяет две общие настройки одной организации. Чтение уже не знает, какую из них выбрать.

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

Как изменить решение

CREATE TABLE settings (
  organization_id bigint NOT NULL,
  scope text,
  value jsonb NOT NULL,
  UNIQUE NULLS NOT DISTINCT (organization_id, scope)
);
INSERT INTO settings VALUES (7, NULL, '{"limit": 10}');
-- Повтор общей настройки организации 7 будет отклонён:
INSERT INTO settings VALUES (7, NULL, '{"limit": 20}');

Теперь общая настройка участвует в уникальности так же, как именованная область. Настройка другой организации с NULL остаётся допустимой: сравнивается вся пара ключей, а не только scope.

Перед изменением действующей схемы найдите дубли с GROUP BY organization_id, scope HAVING count(*) > 1 и решите, какая запись должна остаться. Новое ограничение не выбирает победителя за вас. Если NULL означает не общую область, а неизвестную, объединять такие строки одним ключом может быть неверно.

Что проверить на своих данных

Проверьте четыре случая: повтор NULL в одной организации, NULL в разных организациях, повтор одного непустого scope и разные scope в одной организации. Такой набор показывает, что ограничение защищает именно модель настроек, а не запрещает полезные записи.

Для более старых версий PostgreSQL задача решается двумя частичными уникальными индексами: один по organization_id для scope IS NULL, второй по паре полей для scope IS NOT NULL. Это уже два объекта схемы, которые нужно учитывать при миграциях и UPSERT. Выбор синтаксиса стоит привязывать к версии реальной базы, а не версии клиентского драйвера.

Практика дальше

Следующая практика — запросы, где правильность зависит от нескольких источников и граничных случаев. В курсе «Продвинутый SQL» вы решаете 109 задач на JOIN, окна и CTE, сверяете отчёты и собираете витрину интернет-магазина. Запросы выполняются в браузере на SQLite с автоматической проверкой.

Посмотреть программу и практику курса

Источник: UNIQUE разрешил второй пустой ключ — официальная документация.

Другие задачи по теме: PostgreSQL: запись, блокировки и планы запросов.