Postgres unique constraint not enforcing uniqueness

Question

Here is my constraint:

CREATE UNIQUE INDEX index_subscriptions_on_user_id_and_class_type_id_and_deleted_at
  ON subscriptions
  USING btree
  (user_id, class_type_id, deleted_at);

This query proves the constraint is not actually working:

SELECT id, user_id, class_type_id,deleted_at
FROM subscriptions;

Here is the output:

Why is uniqueness not being enforced?

Answer 1

Unique indexes in Postgres are based on values being equal, but NULL is never equal to anything, including other NULLs. Therefore any row with a NULL deleted_at value is distinct from any other possible row - so you can insert any number of them.

One way around this is to create partial indexes, applying different rules to rows with and without NULLs:

 CREATE UNIQUE INDEX ... ON subscriptions
 (user_id, class_type_id) WHERE deleted_at IS NULL;

 CREATE UNIQUE INDEX ... ON subscriptions
 (user_id, class_type_id, deleted_at) WHERE deleted_at IS NOT NULL;

Answer 2

This happens because of the NULL value in the created_at column. A unique index (or constraint) allows multiple rows with NULL in them.

The only way you can prevent that is to either declare the column as NOT NULL in order to force a value for it, or reduce the unique columns to (user_id, class_type_id)