How to allow multiple blanks (empty strings) using a unique index?

Question

I have a unique index like so in my migration

add_index :table, :name, unique: true

Now the unique constraint allows for multiple nil values however I also want blank values (empty strings "") to bypass unique constraint as well. What is the best way to keep the unique index but also bypass and allow multiple blank strings?

Answer 1

Ref indexes-partial and null_or_empty

CREATE UNIQUE INDEX table_name_constraint 
ON table (name) 
WHERE ((name <> '') IS NOT TRUE);

Ref this for how to write it in the rails migration.

execute <<-SQL
  CREATE UNIQUE INDEX table_name_constraint ON table (name) WHERE ((name <> '') IS NOT TRUE);
SQL