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

Answer 2

@Salil's answer did not work for me. It all looked good in the schema etc but I still got the non unique psql error when trying to save a second record with blank value.

This works for me in a Rails 6 migration (user and username case where a preexisting unique index was already there)

class ChangeUniqueConstraintOnUsername < ActiveRecord::Migration[6.1]
  def up
    remove_index :users, :username, name: :index_users_on_username
    add_index :users, :username, unique: true, where: "username != '' AND username IS NOT NULL"
  end

  def down
    remove_index :users, :username, name: :index_users_on_username
    add_index :users, :username, unique: true
  end
end