В одной из наших систем мы храним информацию о запускаемых процессах в отдельной таблице, где с помощью уникального индекса гарантируется уникальность идентификатора процесса.
Какого же было мое удивление, когда однажды мы обнаружили, что в таблице появились дублирующиеся процессы.

Таблица выглядит примерно так:

CREATE TABLE process_instance
(
  id                  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  process_instance_id uuid   NOT NULL,
  env                 text
);

А вот так обеспечивается уникальность (по нашей задумке):

CREATE UNIQUE INDEX unique_process_instance_id ON process_instance (process_instance_id, env);

Поле env при этом заполняется только в тестовых окружениях, так как они переиспользуют общую базу. Но на проде оно всегда null.

И вот именно на проде мы получили дубликаты:

SELECT * FROM process_instance WHERE process_instance_id = :processInstanceId;

id | process_instance_id | env
---+----------------
1  | 12345678-1234-1234-1234-123456789012 | null
2  | 12345678-1234-1234-1234-123456789012 | null

Как же так вышло? Почему уникальный индекс не сработал? На самом деле, мы пропустили очень логичный, но не очевидный в конкретно этом сценарии момент.

Для проверки уникальности значения сравниваются по равенству. А в SQL в целом и в PostgreSQL в частности, null != null - в любых запросах, где нам нужно сравнивать какое-то значение с null, нужно использовать операторы is null или is not null. Собственно, именно этот факт и подвел нас при проверке уникальности в индексе. Любые строки с совпадающим идентификатором, но env null - считались отличающимися.

Окей, проблему поняли, а как чинить?
Первое что приходит в голову - нужно избавиться от null в индексе. Сделать это можно, например, вот так:

CREATE UNIQUE INDEX unique_process_instance_id ON process_instance (process_instance_id, coalesce(env, ''));

Решение рабочее, но не самое лучшее, поскольку мы подменяем для проверки уникальности null на другое значение. А что если такое значение само по себе может содержаться в поле? Да, для env это, наверное, не реалистичный кейс, но в общей ситуации это может быть проблемой - нужно искать какое-то магическое значение, которое не будет пересекаться с реальными значениями этого поля.
К тому же, для полной утилизации такого индекса при поиске нам пришлось бы использовать coalesce(env, '') и в условии where, что не очень удобно.

Но с PostgreSQL 15 появилось стандартное решение этой проблемы, с помощью модификатора NULLS NOT DISTINCT в индексе:

CREATE UNIQUE INDEX unique_process_instance_id ON process_instance (process_instance_id, env) NULLS NOT DISTINCT;

В таком режиме любые null в индексе считаются равными друг другу, что решает нашу проблему.
Использование такого синтаксиса сразу даёт понять читателю кода чего мы хотели добиться, не заставляя разбираться в замысле coalesce.
Ну и, конечно же, он никак не влияет на механику самого поиска по индексу, поэтому нет необходимости никак модифицировать запросы.