Проверка уникальности работает через =, а NULL = NULL не даёт true. По умолчанию NULL не считается уникальным значением.

Postgres не может доказать равенство двух NULL, поэтому пропускает обе строки.

Простой пример, где это ломается, — составной ключ:

-- UNIQUE (user_id, org_id, deleted_at)

INSERT INTO memberships VALUES (1, 10, NULL);
INSERT INTO memberships VALUES (1, 10, NULL);

-- обе строки будут вставлены

В итоге можно получить две активные записи membership для одного и того же пользователя, потому что ограничение уникальности вообще не срабатывает.

NULLS NOT DISTINCT

Начиная с Postgres 15, уникальные ограничения и индексы могут считать NULL равными:

UNIQUE NULLS NOT DISTINCT (user_id, org_id, deleted_at)

Второй INSERT уже завершится ошибкой.

Но это также запретит две строки с одинаковым значением deleted_at, а не только две активные строки.

Частичный уникальный индекс

Если уникальность нужно обеспечить только для активных строк, используйте partial unique index:

CREATE UNIQUE INDEX ON memberships (user_id, org_id)
WHERE deleted_at IS NULL;

👉 @SQLPortal