Postgres unique constraint does not provide uniqueness

Question

Here is my limitation:

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 that the restriction does not actually work:

 SELECT id, user_id, class_type_id,deleted_at FROM subscriptions; 

Here is the result:

Why uniqueness is not respected?

Answer 1

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

One way is to create partial indexes by applying different rules to rows with and without NULL:

  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 is due to the NULL value in the created_at column. A unique index (or restriction) allows multiple rows with NULL in them.

The only way to prevent this is to either declare the column as NOT NULL to force it to be assigned, or reduce the unique columns to (user_id, class_type_id)