PostgreSQLで大文字小文字問わず検索する

Web サービスの開発をしていると、テキスト検索が必要になることがよくあります。
その際に、テキスト検索の中でもどう扱うか決めるべきことがあります。

• 大文字・小文字
• 全角・半角
• かな・カナ

今回は、PostgreSQL 大文字・小文字を問わない検索方法のやり方を紹介します。

PostgreSQL での大文字・小文字の扱い

基本的なことですが、PostgreSQL では検索時、大文字・小文字を区別します。
簡単に動きを確認します。

まず、検証用のテーブルを作成します。

1CREATE TABLE search_text
2(
3    id serial,
4    body text,
5    CONSTRAINT search_text_pkey PRIMARY KEY (id)
6)

テスト用のデータを投入します。

1INSERT INTO search_text
2  (body)
3VALUES
4  ('ABCDE'),
5  ('abcde'),
6  ('AbcdE'),
7  ('AbCdE'),
8  ('aBcDe')
9;

ここで、ABCDEで検索してみます。

1SELECT * FROM search_text
2WHERE
3  body LIKE '%ABCDE%'

結果は、「ABCDE」のみがヒットし、大文字・小文字が区別されていることが分かります。
ちなみに、MySQLでは、デフォルトで区別されないため、すべてヒットします。

大文字・小文字を区別しない検索

PostgreSQLでは、区別しない方法が用意されています。
標準のSQLではなく、PostgreSQL 拡張のILIKEに変えるだけで区別しないようにできます。

1SELECT * FROM search_text
2WHERE
3  body ILIKE '%ABCDE%'

LIKEをILIKEに変更するだけで、5件すべてがヒットしました。

公式ドキュメントでは以下の箇所に記載されています。

https://www.postgresql.jp/document/13/html/functions-matching.html

現在のロケールに従って大文字小文字を区別しない一致を行うのであれば、LIKEの代わりにILIKEキーワードを使うことができます。 これは標準SQLではなく、PostgreSQLの拡張です。

PostgreSQL 13.1文書 第9章 関数と演算子 9.7.1. LIKE

まとめ

PostgreSQLでは、大文字・小文字を区別します。
区別しないようにする場合は、ILIKEを使用する。

他の全角・半角、かな・カナも区別しないようにする要件の場合は、検索用のフィールドをあらかじめ作成し、大文字・全角・かななどに変換しておいた上で、LIKEで検索した方がよいです。
くれぐれも、検索対象の列を都度、変換して検索しないようにしてください。

mabushiisign

Twitter

B'zとdoaとミスチルと登山が好きな40代のプログラマです。 普段は、SIer企業で働いています。