概要
文字列型（マイグレーションファイルでstringを指定したカラム）について、PostgreSQL では MySQL と異なり、デフォルトで大文字小文字を区別 (case sensitive) します。
そのため、大文字小文字を無視したクエリを発行するためには工夫が必要です。
また、対応方法によっては関数インデックスという機能が使用でき、高速な検索が可能となります。
照合順序
照合順序は特別な設定はしておらず、en_US.UTF-8を利用しています。
解決方法
以下の2パターンの解決方法があります。
- ILIKE を使用する
- LOWER を使用する
インデックスを利用していない状態で両者を試してみましたが、LOWER を使用した場合の方が若干早いように感じました。なんとなくそんな気もしますが、気のせいのような気もします。もっとクエリのコストとかについて勉強しておきたい……。
また、インデックスに関して、以下のような記述が見つかりました。
LOWER(bar) = LOWER(?) will work on MySQL and Postgres, but is likely to perform terribly on MySQL : MySQL won't use its indexes because of the LOWER function. On Postgres you can add a functional index (on LOWER(bar) ) but MySQL doesn't support this.
なけなしの英語力で一言でまとめると、「LOWER 使うと PostgreSQL だったら関数インデックス使えるけど、MySQL だとそれサポートしてないよ！」ということだと思います。
関数インデックス
関数インデックスってなんやねんって感じだったので、調べました。
インデックス列は、基礎をなすテーブルにある列である必要はなく、そのテーブルの１つ以上の列から計算される関数やスカラ式とすることもできます。 この機能は、ある演算結果に基づいた高速テーブルアクセスを行う時に有用です。
どうやら、式や関数に食わせた値に対するインデックスを作成できる機能が PostgreSQL にはあるそうです。また、特にその中でも関数を利用したものを関数インデックスと呼ぶことがあるとのことでした。
つまり、LOWER 使っとけば良いんだよ！ってことですね！たぶん！
記述方法
実際に Rails で記述する方法です。
ILIKE を使用する
PostgreSQL では ILIKE を利用すると、大文字小文字を無視した検索が可能です。
SELECT
tables.*
FROM
tables
WHERE
column ILIKE 'hoge';
ILIKE での検索は、Arel のmatchesを利用すれば以下のように書けます。
tables = Table.arel_table
Table.where(tables[:column].matches('hoge'))
LOWER を使用する
両者に LOWER を用いることで、大文字小文字を無視した結果を得ることができます。
SELECT
tables.*
FROM
tables
WHERE
LOWER(column) = LOWER('hoge');
Rails では次のように書けます。
Table.where('lower(column) = ?'), 'hoge'.downcase)
1 class AddLowerKeywordIndexToTags < ActiveRecord::Migration¬
2 def change¬
3 sql = 'CREATE INDEX tags_lower_keyword_idx ON tags (lower(keyword));'¬
4 ActiveRecord::Base.connection.execute(sql)¬
5 end¬
6 end¬
補足
PostgreSQL で大文字小文字を区別するには、citext という型を使う方法もあります。
こちらは、比較の際に内部で LOWER を適用しているそうです。
関数インデックスに関しては、面倒だったので張っていません。張ったらどれくらい早くなるのか、マイグレーションファイルに書く方法とかないのかとか、気になることはありますが、それはまたの機会に……。
関数インデックスが PostgreSQL 特有の機能だとすると、マイグレーションファイルでいい感じに書くのは無理そうな気もします。
追記
関数インデックスを貼ってみました。