Create lowercase index

[Linus Pettersson]

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

Cheers,

Linus

[Jordon Bedwell]

On Tue, Jul 2, 2013 at 12:13 PM, Linus Pettersson
<linus.pe...@gmail.com> 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 :)

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

[pironim]

How about case insensetive collation for your columns?

[Rick]

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) -- PostgreSQL
CREATE INDEX index_developers_on_name USING btree ON developers (name) -- MySQL

Note: only supported by PostgreSQL and MySQL

[Linus Pettersson]

I'm on Rails 3.2.13 I'm afraid.

I'll just use the execute statement for now :)

[Jonathan Dean]

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?

[Scott Ribe]

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.

> --
> You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-ta...@googlegroups.com.
> To post to this group, send email to rubyonra...@googlegroups.com.
> 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 Ribe
scott...@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice