Чим унікальний індекс відрізняється від обмеження UNIQUE і як працює NULLS NOT DISTINCT?

Обмеження UNIQUE - логічне правило схеми. PostgreSQL реалізує його унікальним індексом, який створює автоматично. Тому для простого випадку різниці в поведінці немає:

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
-- майже те саме, що:
CREATE UNIQUE INDEX users_email_unique ON users (email);

Коли потрібен саме індекс:

• Частковий: унікальність серед частини рядків - CREATE UNIQUE INDEX ... ON users (email) WHERE deleted_at IS NULL. Обмеження з WHERE не буває.
• За виразом: унікальність без урахування регістру - ON users (lower(email)).
• CONCURRENTLY: створити без блокування запису на великій таблиці.

Коли потрібне саме обмеження:

• Зовнішні ключі посилаються на PRIMARY KEY чи UNIQUE-обмеження (або унікальний індекс без умови).
• Відкладена перевірка DEFERRABLE INITIALLY DEFERRED - перевіряти унікальність у кінці транзакції, а не після кожного рядка (наприклад, при перестановці позицій).
• ON CONFLICT ON CONSTRAINT за іменем.

NULL і унікальність. За стандартом SQL NULL не дорівнює NULL, тож у колонці з UNIQUE може бути скільки завгодно рядків з NULL. Часто це і потрібно (необов'язковий email). Але інколи - ні: «у користувача може бути лише одна адреса без типу».

NULLS NOT DISTINCT (PostgreSQL 15+) змінює правило - NULL вважаються однаковими:

CREATE UNIQUE INDEX one_default_address ON addresses (user_id, type) NULLS NOT DISTINCT;

Тепер для одного user_id дозволено лише один рядок з type IS NULL.

Докладніше в документації: Унікальні індекси