Rails migration - add unique index allowing skipping null values

Question

I wanted like to add unique constraint to column name in existing table psql

created table (PostgreSQL) :

class CreateRequests < ActiveRecord::Migration
  def change
    create_table :requests do |t|
      t.integer :user_id
      t.string :name
      t.string :address
      t.timestamps null: true
    end
    add_index :requests, :user_id
  end
end

So I added validation uniqueness in model

class Request < ModelBase
  belongs_to :user
  validates :user, presence: true
  validates :name, uniqueness: true, allow_blank: true
  ...

and migration like this:

def change
    add_index :user_requests, :name, unique: true
end

but I noticed that in some cases name can be empty can I add_index with condition were :name is null / not null?

Edit: yes, I want those empty values to stay in the database (I don't need to modify past request). I think that I need to edit migration to be more accurate to the actual state of db.

Answer 1

No one answer so I'm adding my solution migration should look like that:

def change
    add_index :user_requests, :name, unique: true, where: 'name IS NOT NULL'
end

(validation still: validates :name, uniqueness: true, allow_blank: true)

Answer 2

Use following

validates :name, uniqueness: true, if: 'name.present?'

For index may be you try

add_index :user_requests, :name, unique: true, where: 'name IS NOT NULL'

Answer 3

proper format to make uniqueness

validates :name, presence: true, uniqueness: {case_sensitive: false},format: { without: /\s/,message: 'is invalid (spaces are not allowed)'}

Answer 4

You can try validations here

validates :name, presence: true, uniqueness: true,

Where, presence: true will validate for empty or not and uniqueness: true will validate for unique name of user.

Answer 5

Add presence: true also like

validates :name, uniqueness: true, presence: true

You can add default value in your migration like

class AddDefaultToRequests < ActiveRecord::Migration
  def up
    change_column :requests, :name, default: ''
  end

  def down
    change_column :requests, :name
  end
end