Adding postgres index with condition.

February 12, 2018

For example, If you need only 1 admin on each project.

class AddUsers < ActiveRecord::Migration[5.1]
  def change
    create_table :users do |t|
      t.integer :project_id, null: false
      t.string :name, null: false
      t.integer :role, null: false

      t.timestamps
    end

    reversible do |direction|
	  direction.up do
        # Partial unique index

        execute <<-SQL

          CREATE UNIQUE INDEX unique_user_role_in_project ON users (project_id, role) WHERE role = 'admin';
        SQL

      end

      direction.down do
        # Remove unique index

        execute <<-SQL

          DROP INDEX IF EXISTS unique_user_role_in_project;
        SQL

      end
    end
  end
end
Adding postgres index with condition. - February 12, 2018 - {"name"=>"Stanislav Knyazev", "email"=>"extazystas@gmail.com"}