DEV Community

Cover image for How to create unique index skipping empty values in Rails + Postgres?
Jędrzej (NJ) Urbański
Jędrzej (NJ) Urbański

Posted on

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
Enter fullscreen mode Exit fullscreen mode

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

add_index :organizations, :invitation_code, unique: true
Enter fullscreen mode Exit fullscreen mode

And add validation in model:

validates :invitation_code, uniqueness: true
Enter fullscreen mode Exit fullscreen mode

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"]}
Enter fullscreen mode Exit fullscreen mode

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"]}
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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

validates :invitation_code, uniqueness: { allow_blank: true }
Enter fullscreen mode Exit fullscreen mode

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> 
Enter fullscreen mode Exit fullscreen mode

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"]}
Enter fullscreen mode Exit fullscreen mode

Works like a charm. Happy hacking! 🧑‍💻

Top comments (0)