I’m sorting some columns like this: MyModel.order(“LOWER(column) ASC”)… But these queries are quite slow. I’m on Postgres by the way.
Does Rails support creating a lowercase index for these situations? I know Postgres has support for it and I guess I can create one like this (found on SO):
execute "CREATE UNIQUE INDEX index_products_on_lower_name ON products USING btree (lower(name));"
But does Rails have support for creating it? Don’t like to use execute() if there is a better way
Not that I've ever seen but that's expected tbh.
How about case insensetive collation for your columns?
Direct from Rails4.0 ActiveRecord documentation:
Creating an index with a specific method
add_index(:developers, :name, using: 'btree')
CREATE INDEX index_developers_on_name ON developers USING btree (name) -- PostgreSQL
CREATE INDEX index_developers_on_name USING btree ON developers (name) -- MySQL
Note: only supported by PostgreSQL and MySQL
I’m on Rails 3.2.13 I’m afraid.
I’ll just use the execute statement for now
That doesn’t address the lower(name) portion of the question, just the part where it specifies btree. Any idea how to do the lowercase portion?
create index index_developers_on_name on developers((lower(name)));
Note the extra set of () to indicate that lower is a functional expression instead of a column name.