PostgreSQLで大文字と小文字を区別しないクエリを書く方法

大文字と小文字が異なっていてもテキストを一致させたい場合、パターン検索には ILIKE を使い、比較する値を LOWER または UPPER で正規化するか、列で通常の動作として大文字と小文字を区別しない等価比較を行うなら CITEXT 型を使います。適切な方法は、パターン検索が必要か、列をどのように検索するか、利用できるインデックスやロケール設定によって異なります。

以下の例では、id、age、type 列を持つ animal テーブルを使い、type が Kitten の行も扱います。SQLは説明用であり、PostgreSQLサーバーでは実行していません。

ILIKEで大文字と小文字を区別しないパターン検索を行う

大文字と小文字を区別しない等価比較には、type ILIKE 'kitten' と書きます。値の一部に一致するパターンを検索するには、type ILIKE '%kitten%' のようにワイルドカードを使います。% は任意の長さの文字列に、_ は任意の1文字に一致します。ILIKE は PostgreSQL の LIKE に相当する、大文字と小文字を区別しない演算子です。その動作は有効なロケールに従います。

通常の LIKE は大文字と小文字を区別するため、照合順序が大文字と小文字を区別する場合、type LIKE 'kitten' は Kitten に一致しません。SIMILAR TO はSQLの正規表現に似たパターン構文を使う一方、ILIKE はより単純な LIKE のワイルドカード構文を使います。用途が異なるため、すべてのクエリで互いに置き換えられるわけではありません。PostgreSQLでは ~~* 演算子も ILIKE の別表記として使えます。

等価比較に近い一致が必要なら、完全なパターンを使います。より広いパターンに一致させたい場合に限って、% や _ を加えてください。

LOWERまたはUPPERで比較する両側を正規化する

大文字と小文字を区別せずに等価比較するには、列と検索値の両方に同じ変換を適用します。たとえば LOWER(type) = LOWER('kitten') のように書きます。両側に UPPER を使う方法もあります。LOWER はデータベースのロケールに従ってテキストを変換する関数です。INITCAP は単語の先頭を大文字にする関数であり、大文字と小文字を区別しない比較の一般的な代替手段ではありません。

大文字・小文字の変換や比較は、特に言語固有の文字について、ロケールや照合順序に左右されることがあります。テキスト変換が英語にしか使えない、あるいはすべてのロケールで全く同じ文字処理が行われる、と考えないでください。データと比較ルールに合ったデータベースのロケールと照合順序を選び、動作を確認してください。

大文字と小文字を区別しない検索にインデックスを追加する

type に対する通常のインデックスは、LOWER(type) を条件にした検索で同じ式のインデックスとして利用できるとは限りません。正規化した等価比較には、クエリと同じ式を使う式インデックスを作成します。たとえば CREATE INDEX animal_type_lower_idx ON animal (LOWER(type)); のようにします。同様に、UPPER(type) を使うクエリには、UPPER(type) のインデックスが利用できる場合があります。

インデックスが役立つのは、その式と演算子がクエリに対応し、かつプランナーがインデックスを選択した場合です。クエリが速くなる保証はありません。PostgreSQLの pg_trgm 拡張機能を使うと、対応する LIKE や ILIKE 検索向けにGINまたはGiSTのトライグラムインデックスを用意できます。効果はパターン、テーブル、ワークロードによって異なります。代表的なデータで実行計画と所要時間を確認してから、インデックスを選んでください。

大文字と小文字を無視した重複値を拒否する必要がある場合は、CREATE UNIQUE INDEX animal_type_lower_unique ON animal (LOWER(type)); のような一意な式インデックスで、その式が対象とする値にルールを適用できます。これはスキーマ設計上の判断です。適用前に、意図したデータルールに合っていることを確認してください。

大文字と小文字を区別しない列にはCITEXTを使う

CITEXT 拡張機能は、比較時に大文字と小文字を区別しないテキスト型を提供します。そのため、通常の等価比較で大文字と小文字の表記が異なる値を一致させられます。CREATE EXTENSION citext; で拡張機能を有効にし、たとえば列を type CITEXT のように宣言します。すると、SELECT id, age, type FROM animal WHERE type = 'kitten'; のようなクエリで、型の大文字と小文字を区別しない比較動作に従って、保存値 Kitten に一致させられます。

列の比較で常に大文字と小文字を区別しない場合、CITEXT は便利です。ただし、ほかの方法より常に高速または低コストになるわけではなく、インデックス、照合順序、ワークロードを考慮する必要もなくなりません。データベースで拡張機能が利用可能で、有効になっている必要があります。採用する前に、使用するロケールとデータにおける比較動作を確認してください。

正規化の考え方を示すため、以下のPython例では、ASCII文字列の大文字・小文字の表記を比較します。この例が示すのはPythonの文字列処理だけであり、PostgreSQLのSQLを実行するものでも、データベースの照合順序の動作を示すものでもありません。

stored_type = "Kitten"
search_type = "kitten"
print(stored_type.lower() == search_type.lower())

True