Sources Contact Advanced Search Tutorials

An Interest In:

Web News this Week

Search Archive

Some of Our Sources

View All Sources

Help Webnuz

Referal links:

October 10, 2022 07:43 pm GMT

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  endend

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_codeirb(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_codeirb(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_codeirb(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_codeirb(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!

Original Link: https://dev.to/jedrzejurbanski/how-to-create-unique-field-only-if-not-empty-for-rails-7-and-postgres-2i8g

Share this article:

View Full Article

Dev To

An online community for sharing and discovering great ideas, having debates, and making friends

More About this Source Visit Dev To