How to create unique index skipping empty values in Rails + Postgres?
Tags: rails, ruby, postgres
Published: 2022-10-10 19:43:26

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.
```ruby
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`:
```ruby
add_index :organizations, :invitation_code, unique: true
```
And add validation in model:
```ruby
validates :invitation_code, uniqueness: true
```
Okay, let's see the result:
```ruby
# 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?
```ruby
# 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.
```ruby
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:
```ruby
validates :invitation_code, uniqueness: { allow_blank: true }
```
And it all set. 🎉

## Test
Let's run migration and quickly test our code in console:
```ruby
# 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:
```ruby
# 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! 🧑‍💻