How to create unique index skipping empty values in Rails + Postgres?

Having unique column in rails is easy thing to do. But what if you want to have unique column skipping null values?

Problem

Let's imagine we have application, where invite people to your organisation by sending them invitation code. These should be unique for every organisation, and empty in case we did not generated it yet.

For that let's create organisation model and migration with invitation_code field.

class CreateOrganizations < ActiveRecord::Migration[7.0]
  def change
    create_table :organizations do |t|
      t.string :invitation_code, null: true
    end
  end
end

That's great for start. Now let's create unique index for invitation_code:

add_index :organizations, :invitation_code, unique: true

And add validation in model:

validates :invitation_code, uniqueness: true

Okay, let's see the result:

# for non-empty invitation_code
irb(main):004:0> org = Organization.create(invitation_code: "f00b4r")
=> #<Organization id: "a8dc7fd8-7724-445c-bed4-72c94af99151", invitation_code: "f00b4r">       
irb(main):005:0> org = Organization.create(invitation_code: "f00b4r")
=> #<Organization id: nil, invitation_code: "f00b4r">
irb(main):006:0> org.errors.messages
=> {:invitation_code=>["has already been taken"]}

It's unique for non-empty values, but for empty?

# for empty invitation_code
irb(main):001:0> Organization.create()
=> #<Organization id: nil, invitation_code: nil>
irb(main):002:0> org = Organization.create()
=> #<Organization:0x0000ffff897f2a40 id: nil, invitation_code: nil>
irb(main):003:0> org.errors.messages
=> {:invitation_code=>["has already been taken"]}

Well... as in migration we allow invitation_code to be null, we will have an db error, when creating new record. For that we need to modify our migration.

Solution

We need to modify creating an index as well as model validation.

Let's take a focus on migration first. We need to change just creating index with where statement, so it's unique in scope of non null values.

add_index :organizations, :invitation_code,
  unique: true,
  where: 'invitation_code IS NOT NULL',
  name: 'unique_not_null_invitation_code'

Great, we're all set with db. Now rails model:

validates :invitation_code, uniqueness: { allow_blank: true }

And it all set. 🎉

Test

Let's run migration and quickly test our code in console:

# for empty invitation_code
irb(main):001:0> Organization.create()
=> #<Organization id: "88174146-19c6-40e6-b675-ea04c3e4238f", invitation_code: nil>
irb(main):002:0> Organization.create()
=> #<Organization id: "61d630d1-d244-47e4-af46-880a021a26ca", invitation_code: nil> 

Great, two organisation created with null invitation_code. Now try with non-empty code:

# for non-empty invitation_code
irb(main):003:0> Organization.create(invitation_code: "f00b4r")
=> #<Organization id: "8b2ed6e7-76db-4f76-9288-69267ebca5f7", invitation_code: "f00b4r">
irb(main):004:0> org=Organization.create(invitation_code: "f00b4r")
=> #<Organization id: nil, invitation_code: "f00b4r">
irb(main):005:0> org.errors.messages
=> {:invitation_code=>["has already been taken"]}

Works like a charm. Happy hacking! 🧑‍💻