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?
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
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"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With