Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Advanced usage of add_index

In my Ruby on Rails project I have migration that creates case insensitive index for string column.

class AddCeCodeToUsers < ActiveRecord::Migration
  def change
    add_column :users, :ce_code, :string

    execute <<-SQL
      CREATE UNIQUE INDEX index_users_on_lower_ce_code_index
        ON users USING btree (lower(ce_code));
    SQL
  end
end

This works as expected but I want to know is there any way to do the same using rails built_in add_index method?

like image 657
Mateusz Urbański Avatar asked Sep 01 '25 18:09

Mateusz Urbański


2 Answers

ActiveRecord 5.2 or greater supports indexing on lower(field):

class AddEmailInsensitiveIndex < ActiveRecord::Migration[5.2]
  def change
    change_table :users do |t|
      t.index "lower(email)", name: "index_users_on_lower_case_email"
    end
  end
end

https://www.joshmcarthur.com/til/2019/10/15/fancy-postgres-indexes-with-activerecord.html

like image 125
AlejandroVD Avatar answered Sep 04 '25 06:09

AlejandroVD


For mysql try:

add_index "users", "lower(ce_code)", name: "index_users_on_lower_ce_code"

For PG try:

install gem "schema_plus_pg_indexes"

t.index expression: "lower(ce_code)", name: "index_users_on_lower_ce_code"
like image 45
shilovk Avatar answered Sep 04 '25 07:09

shilovk