Create lowercase index

Hi

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 :slight_smile:

Cheers,
Linus

On Tue, Jul 2, 2013 at 12:13 PM, Linus P.
[email protected] wrote:

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 :slight_smile:

Not that I’ve ever seen but that’s expected tbh.

Direct from Rails4.0 ActiveRecord documentation:

Creating an index with a specific method

add_index(:developers, :name, using: ‘btree’)

generates:

CREATE INDEX index_developers_on_name ON developers USING btree (name)
– PostgreSQLCREATE INDEX index_developers_on_name USING btree ON
developers (name) – MySQL

Note: only supported by PostgreSQL and MySQL

How about case insensetive collation for your columns?

Straight from rails4.0 documentation:
http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html

Creating an index with a specific method

add_index(:developers, :name, using: ‘btree’)

generates:

CREATE INDEX index_developers_on_name ON developers USING btree (name)
– PostgreSQLCREATE 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 :slight_smile:

Den onsdagen den 3:e juli 2013 kl. 20:30:38 UTC+2 skrev Rick:

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?

For PostgreSQL:

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.

On Aug 14, 2013, at 6:46 AM, Jonathan D. [email protected]
wrote:

CREATE INDEX index_developers_on_name ON developers USING btree (name) –
PostgreSQL
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):
To view this discussion on the web visit
https://groups.google.com/d/msgid/rubyonrails-talk/7e793c66-22d6-4270-86c3-580ed226e719%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.


Scott R.
[email protected]
http://www.elevated-dev.com/
(303) 722-0567 voice