Uniq index with one optional column, which can be NULL

When you want to do it, Rails allows creating this index.

In this case, table_invitation_id column is optional.

Example below:

add_index :orders, [:creator_id, :brunch_series_id, :table_invitation_id], unique: true

But then we are able to create two the same records and it’s obscure.

Example below doesn’t raise ActiveRecord::RecordNotUnique: PG::UniqueViolation

2.times { Order.create(creator_id: 1, brunch_series_id: 10, table_invitation_id: nil } 

Conclusion

We are not able to do it, Postgres doesn’t have support for it “[…] Null values are not considered equal” - https://www.postgresql.org/docs/current/indexes-unique.html

We just have to create two indexes one when a value is not null and the second when is null.

add_index :orders, [:creator_id, :brunch_series_id, :table_invitation_id],
              unique: true,
              where: 'table_invitation_id IS NOT NULL',
              name: 'uniq_order_with_table_invitation_id'
add_index :orders, [:creator_id, :brunch_series_id],
              unique: true,
              where: 'table_invitation_id IS NULL',
              name: 'uniq_order_without_table_invitation_id'

Then index works fine and code below exists only one Order

2.times { Order.create(creator_id: 1, brunch_series_id: 10, table_invitation_id: nil } 

Tweet