PostgreSQL ILIKE｜大文字小文字を区別しない検索と索引

ILIKEとは、LIKEと同じパターン検索を、大文字と小文字を区別せずに行うPostgreSQL独自の演算子です。 WHERE email ILIKE 'taro%' と書くだけで Taro も TARO も見つかります。書き方は手軽ですが、ふつうのB-treeインデックスはほぼ使われないため、行数が増えると検索時間が伸びていきます。

この記事では、ILIKEの書き方とエスケープに加えて、大文字小文字を区別しない検索を速くする5つの方式（ILIKE、lower()＋式インデックス、citext、pg_trgmのGINインデックス、ICUの非決定的照合順序）を、20万行のテーブルで取ったEXPLAIN ANALYZEの結果で比べます。全角英字やトルコ語の i の挙動、大文字小文字を無視したユニーク制約の作り方も、再現できるSQLつきで載せています。

確認環境：Ubuntu 24.04、PostgreSQL 18.4（データベースのロケールは C.UTF-8、ICU照合順序つき。psql 16.15 から接続）。バージョン差の確認に PostgreSQL 16.15 を使いました。実行時間は手元の1回の計測値で、環境によって変わります。

大文字小文字を区別しない検索の早見表

20万行の members テーブル（後述）で、5方式それぞれがインデックスを使えたかどうかの結果です。

演算子の対応は次のとおりです。

ILIKEの基本的な書き方

検証用のテーブルを作る

以降の例はすべて、次のテーブルで実行しています。メールアドレスの先頭が User / USER / user と混ざった20万行に、目印の2行を足しています。

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE members (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email  text NOT NULL,
  name   text NOT NULL
);
INSERT INTO members (email, name)
SELECT CASE WHEN g % 3 = 0 THEN 'User' WHEN g % 3 = 1 THEN 'USER' ELSE 'user' END
       || g || '@Example.com',
       'member' || g
FROM generate_series(1, 200000) g;
INSERT INTO members (email, name) VALUES
  ('Taro.Yamada@Example.com', 'Taro'), ('100%_off@example.com', 'promo');
ANALYZE members;

LIKEとILIKEの違い

SELECT email FROM members WHERE email LIKE 'taro%';

 email 
-------
(0 rows)

SELECT email FROM members WHERE email ILIKE 'taro%';

          email          
-------------------------
 Taro.Yamada@Example.com
(1 row)

LIKEは taro と Taro を別の文字列として扱い、ILIKEは同じとして扱います。 ワイルドカードはLIKEと共通で、% が0文字以上の任意の文字列、_ が任意の1文字です。

演算子表記でも書ける

ILIKEは内部では ~~* という演算子に変換されます。EXPLAINの出力にはこの演算子表記で出てくるので、覚えておくと実行計画が読みやすくなります。

SELECT email FROM members WHERE email ~~* 'TARO.YAMADA@%';

          email          
-------------------------
 Taro.Yamada@Example.com
(1 row)

まとめて確かめた結果です。

SELECT 'ABC' ILIKE 'abc' AS a, 'ABC' ~~* 'a_c' AS b, 'ABC' !~~* 'abc' AS c,
       'ABC' ~* '^abc$' AS d, 'ABC' ILIKE 'ab' AS e;

 a | b | c | d | e 
---+---+---+---+---
 t | t | f | t | f
(1 row)

e が f（false）なのは、ILIKEもLIKEと同じく文字列全体との一致を見るためです。ab で始まるものを探すなら 'ab%' と書きます。

NOT ILIKEで「含まないもの」を探す

SELECT email FROM members WHERE email NOT ILIKE '%@example.com';

 email 
-------
(0 rows)

ドメインが @Example.com と @example.com で混ざっていても、ILIKEなら同じドメインとして扱われ、該当なし（全員が同じドメイン）とわかります。

%や_そのものを検索するエスケープ

% や _ を文字として探したいときは、直前にエスケープ文字を置きます。PostgreSQLの既定のエスケープ文字はバックスラッシュです（公式ドキュメント「9.7. Pattern Matching」）。

まず、エスケープなしの場合です。

SELECT count(*) FROM members WHERE email ILIKE '100%_OFF%';

この例ではたまたま1行だけが返りましたが、% は任意の文字列、_ は任意の1文字として働いています。別の値で比べると違いがわかります。

SELECT '1000X-OFF@example.com' ILIKE '100%_OFF%' AS no_escape,
       '1000X-OFF@example.com' ILIKE '100\%\_off%' AS escaped;

 no_escape | escaped 
-----------+---------
 t         | f

エスケープなしだと、% も _ も含まない値にまで一致しています。文字として一致させるには次のように書きます。

SELECT email FROM members WHERE email ILIKE '100\%\_off%';

        email         
----------------------
 100%_off@example.com
(1 row)

エスケープ文字を変えたいときは ESCAPE 句を使います。

SELECT email FROM members WHERE email ILIKE '100!%!_OFF%' ESCAPE '!';

        email         
----------------------
 100%_off@example.com
(1 row)

利用者が入力した検索語をパターンに埋め込むときは、%・_・バックスラッシュの3文字を先にエスケープしておくと、意図しない広い検索になるのを防げます。アプリ側で置換するか、SQL側で replace() を重ねて処理します。

ILIKEはふつうのB-treeインデックスを使わない

ここからが本題です。email 列にB-treeインデックスを作ってから、ILIKEで検索します。

CREATE INDEX members_email_idx ON members (email);

EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF)
SELECT * FROM members WHERE email ILIKE 'taro.yamada@%';

                              QUERY PLAN                              
----------------------------------------------------------------------
 Seq Scan on members (actual time=257.609..257.611 rows=1.00 loops=1)
   Filter: (email ~~* 'taro.yamada@%'::text)
   Rows Removed by Filter: 200001
 Planning Time: 0.256 ms
 Execution Time: 257.628 ms
(5 rows)

（以降の実行計画は、Planning Timeなど一部の行を省いて要点を載せている箇所があります。）インデックスがあっても Seq Scan（全行の読み取り） になり、1件を探すのに20万行を調べています。完全一致の形（ILIKE 'taro.yamada@example.com'）でも中間一致（ILIKE '%yamada%'）でも、同じくSeq Scanで、それぞれ331ms、294msでした。

理由は、B-treeが「文字の並び順」でたどる構造だからです。taro で始まる候補には Taro・TARO・tARO などがあり、並び順の上ではばらばらの位置にあるため、範囲を1つに絞れません。

補足1：LIKEの前方一致にも演算子クラスが関係する

大文字小文字を区別するLIKEでも、データベースのロケールが C 以外のときは、通常のB-treeでは前方一致にインデックスが使われません。今回の環境（C.UTF-8）での結果です。

EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF)
SELECT * FROM members WHERE email LIKE 'Taro.Yamada@%';

 Seq Scan on members (actual time=25.548..25.548 rows=1.00 loops=1)
   Filter: (email ~~ 'Taro.Yamada@%'::text)
   Rows Removed by Filter: 200001
 Execution Time: 25.561 ms

text_pattern_ops という演算子クラスを付けたインデックスを作ると、Index Scanになります。

CREATE INDEX members_email_pattern_idx ON members (email text_pattern_ops);

 Index Scan using members_email_pattern_idx on members (actual time=0.033..0.034 rows=1.00 loops=1)
   Index Cond: ((email ~>=~ 'Taro.Yamada@'::text) AND (email ~<~ 'Taro.YamadaA'::text))
   Filter: (email ~~ 'Taro.Yamada@%'::text)
 Execution Time: 0.049 ms

後述のlower()方式でも、この text_pattern_ops を付けるのがポイントになります。

補足2：先頭が英字以外ならILIKEでもB-treeが使われる

パターンの先頭が数字や記号だけ（大文字小文字の違いがない文字）で始まるときは、ILIKEでも上の text_pattern_ops インデックスが使われました。

EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF)
SELECT * FROM members WHERE email ILIKE '100\%%';

 Index Scan using members_email_pattern_idx on members (actual time=0.012..0.013 rows=1.00 loops=1)
   Index Cond: ((email ~>=~ '100%'::text) AND (email ~<~ '100&'::text))
   Filter: (email ~~* '100\%%'::text)
 Execution Time: 0.025 ms

一方、同じインデックスがある状態でも ILIKE 'taro.yamada@%' はSeq Scan（294ms）のままでした。英字で始まるパターンを速くしたいなら、次の方式のどれかを選びます。

方式1：lower()と式インデックス

列の値と検索語の両方を小文字にそろえてから比べる方法です。lower(email) という式にインデックスを作ります（式インデックス）。

CREATE INDEX members_email_lower_idx ON members (lower(email) text_pattern_ops);
ANALYZE members;

完全一致

EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF)
SELECT * FROM members WHERE lower(email) = lower('TARO.yamada@example.com');

 Index Scan using members_email_lower_idx on members (actual time=0.032..0.032 rows=1.00 loops=1)
   Index Cond: (lower(email) = 'taro.yamada@example.com'::text)
   Index Searches: 1
 Planning Time: 0.242 ms
 Execution Time: 0.061 ms

前方一致

EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF)
SELECT * FROM members WHERE lower(email) LIKE lower('TARO.yamada@') || '%';

 Index Scan using members_email_lower_idx on members (actual time=0.008..0.009 rows=1.00 loops=1)
   Index Cond: ((lower(email) ~>=~ 'taro.yamada@'::text) AND (lower(email) ~<~ 'taro.yamadaA'::text))
   Filter: (lower(email) ~~ 'taro.yamada@%'::text)
   Index Searches: 1
 Planning Time: 0.175 ms
 Execution Time: 0.015 ms

ILIKEで257msだった前方一致が0.015msになりました。WHERE句の左辺を、インデックスを作った式（lower(email)）とまったく同じ形で書くことが条件です。email ILIKE ... と書いた場合、このインデックスは使われません。

中間一致はSeq Scanのまま

EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF)
SELECT * FROM members WHERE lower(email) LIKE '%yamada%';

 Gather (actual time=97.209..98.905 rows=1.00 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   ->  Parallel Seq Scan on members (actual time=90.169..90.170 rows=0.50 loops=2)
         Filter: (lower(email) ~~ '%yamada%'::text)
         Rows Removed by Filter: 100000
 Execution Time: 98.919 ms

B-treeは先頭から絞り込む構造なので、% で始まるパターンには使えません。中間一致が必要なら方式3のpg_trgmを使います。

方式2：citext型にする

citextは、大文字小文字を区別しない文字列型を追加する拡張です。公式ドキュメントによると、比較のたびに内部で lower 相当の変換をしてから比べます。列の型を変えるだけで、= もLIKEも区別なしになります。

CREATE TABLE members_ci (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email citext NOT NULL
);
INSERT INTO members_ci (email) SELECT email FROM members;
CREATE INDEX members_ci_email_idx ON members_ci (email);
ANALYZE members_ci;

SELECT email FROM members_ci WHERE email = 'TARO.YAMADA@EXAMPLE.COM';

          email          
-------------------------
 Taro.Yamada@Example.com
(1 row)

保存されている値は元の大文字小文字のまま返ります。= での検索はふつうのB-treeインデックスが使われます。

 Index Scan using members_ci_email_idx on members_ci (actual time=0.016..0.016 rows=1.00 loops=1)
   Index Cond: (email = 'TARO.YAMADA@EXAMPLE.COM'::citext)
   Index Searches: 1
 Execution Time: 0.024 ms

LIKEも大文字小文字を区別せずに一致しますが、こちらはSeq Scanでした。

EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF)
SELECT * FROM members_ci WHERE email LIKE 'taro.yamada@%';

 Seq Scan on members_ci (actual time=249.760..249.762 rows=1.00 loops=1)
   Filter: (email ~~ 'taro.yamada@%'::citext)
   Rows Removed by Filter: 200001
 Execution Time: 249.782 ms

citextは「SQLを書き換えずに = とユニーク制約を区別なしにしたい」ときに便利です。なお、citextの公式ドキュメントには、非決定的照合順序（方式4）の利用も検討するよう案内があり、text型より比較の効率が下がること、B-treeの重複排除（deduplication）が使えないことが制限として挙げられています。

方式3：pg_trgmのGINインデックス

pg_trgmは、文字列を3文字ずつの断片（トライグラム）に分けて索引にする拡張です。ILIKEのまま、前方一致でも中間一致でもインデックスが使えるようになるのが特長です。

CREATE INDEX members_email_trgm_idx ON members USING gin (email gin_trgm_ops);

中間一致

EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF)
SELECT * FROM members WHERE email ILIKE '%yamada%';

 Bitmap Heap Scan on members (actual time=0.024..0.025 rows=1.00 loops=1)
   Recheck Cond: (email ~~* '%yamada%'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on members_email_trgm_idx (actual time=0.013..0.013 rows=1.00 loops=1)
         Index Cond: (email ~~* '%yamada%'::text)
         Index Searches: 1
 Planning Time: 0.370 ms
 Execution Time: 0.200 ms

インデックスなしで294msだった中間一致が0.2msになりました。SQLは email ILIKE '%yamada%' のままで、書き換えは不要です。

前方一致・完全一致・正規表現

同じインデックスで、ほかの形も確かめました。

大文字小文字を区別しない正規表現（~*）にも使われています。

2文字以下の検索語ではSeq Scanになる

最後の '%ya%' だけSeq Scanになりました。検索語から取り出せるトライグラムを見ると理由がわかります。

SELECT show_trgm('ya') AS two, show_trgm('yamada') AS six;

         two         |                 six                 
---------------------+-------------------------------------
 {"  y"," ya","ya "} | {"  y"," ya",ada,ama,"da ",mad,yam}

show_trgm は単語の前後に空白を補って分割しますが、%ya% のように前後が任意の文字列だと、空白を含む断片は使えず、3文字そろった断片が1つも取れません。公式ドキュメントにも、検索語から取り出せるトライグラムが多いほど索引検索が効果的になること、取り出せるトライグラムがないパターンはインデックス全体の走査になることが書かれています。検索語は3文字以上を条件にすると、安定してインデックスが使われます。

インデックスのサイズ

  tbl  |  btree  | lower_idx |  trgm   
-------+---------+-----------+---------
 15 MB | 7960 kB | 7960 kB   | 6976 kB

今回のデータ（似た形の短いメールアドレス）ではGINのほうが少し小さくなりました。文字列が長く多様なほどトライグラムの種類が増えるので、サイズと更新時の負荷は実データで確かめてから決めるのがおすすめです。

方式4：非決定的照合順序（ICU）

照合順序（collation）は、文字列の比較と並び順のルールです。ICUの「非決定的（nondeterministic）」な照合順序を使うと、バイト列が違っても「等しい」と判定する比較を列に持たせられます。公式ドキュメント「23.2.2.4. Nondeterministic Collations」に載っている、大文字小文字を区別しない照合順序の定義は次のとおりです。

CREATE COLLATION case_insensitive (
  provider = icu, locale = 'und-u-ks-level2', deterministic = false
);

CREATE TABLE members_nd (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text COLLATE case_insensitive NOT NULL
);
INSERT INTO members_nd (email) SELECT email FROM members;
CREATE INDEX members_nd_email_idx ON members_nd (email);
ANALYZE members_nd;

等価比較はインデックスが使われる

EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF)
SELECT * FROM members_nd WHERE email = 'TARO.YAMADA@EXAMPLE.COM';

 Index Scan using members_nd_email_idx on members_nd (actual time=0.013..0.014 rows=1.00 loops=1)
   Index Cond: (email = 'TARO.YAMADA@EXAMPLE.COM'::text)
   Index Searches: 1
 Execution Time: 0.023 ms

型は text のまま、= だけで大文字小文字を区別しない検索になります。

LIKEは18から使えるが、インデックスは使われない

PostgreSQL 18のリリースノートに「Allow LIKE with nondeterministic collations」とあり、18からは非決定的照合順序の列にLIKEが使えます。

SELECT email FROM members_nd WHERE email LIKE 'taro.yamada@%';

          email          
-------------------------
 Taro.Yamada@Example.com
(1 row)

実行計画はSeq Scanで、前方一致が145ms、中間一致（LIKE '%yamada%'）は2,754msでした。今回の計測では5方式のなかで中間一致がいちばん時間のかかる結果です。

同じLIKEをPostgreSQL 16.15で実行すると、次のエラーで止まります。

ERROR:  nondeterministic collations are not supported for LIKE

ILIKEと正規表現は18でもエラーになる

SELECT email FROM members_nd WHERE email ILIKE 'taro.yamada@%';

ERROR:  nondeterministic collations are not supported for ILIKE

SELECT email FROM members_nd WHERE email ~* 'taro';

ERROR:  nondeterministic collations are not supported for regular expressions

公式ドキュメントのILIKEの説明にも、非決定的照合順序には対応していないと書かれています。この列でパターン検索が必要なときは、LIKEを使う（18以降）か、email COLLATE "C" のように式へ別の照合順序を付けてから検索します。

非決定的照合順序は「等価比較・ユニーク制約・JOIN・GROUP BYを区別なしにしたい」用途に向いています。 公式ドキュメントでは、性能面の負担があること、B-treeの重複排除が使えないことが注意点として挙げられています。

EXPLAIN ANALYZEの結果まとめ

20万2行のテーブルで、目印の1行を探したときのExecution Timeです。

選び方を流れにすると次のようになります。

flowchart TD
  A["大文字小文字を区別しない検索"] --> B{"中間一致が必要？"}
  B -->|はい| C["pg_trgm の GIN インデックス + ILIKE"]
  B -->|いいえ| D{"前方一致が必要？"}
  D -->|はい| E["lower() の式インデックス（text_pattern_ops）"]
  D -->|いいえ 完全一致だけ| F{"SQLを書き換えられる？"}
  F -->|はい| E
  F -->|いいえ| G["citext 型 または 非決定的照合順序"]

全角英字・アクセント付き文字・トルコ語のiの挙動

ILIKEとlower()の「大文字小文字」の判定は、データベースのロケール（LC_CTYPE）や指定した照合順序に従います。ここは環境で結果が変わる部分なので、自分のデータベースで同じSQLを流して確かめるのがおすすめです。今回の環境の設定は次のとおりです。

SELECT datcollate, datctype, datlocprovider FROM pg_database
WHERE datname = current_database();

 datcollate | datctype | datlocprovider 
------------+----------+----------------
 C.UTF-8    | C.UTF-8  | c

全角英字とアクセント付き文字

SELECT 'ＡＢＣ' ILIKE 'ａｂｃ' AS zenkaku_ilike, lower('ＡＢＣ') AS lower_zen,
       'ＡＢＣ' ILIKE 'abc' AS zen_han, 'ÀÉ' ILIKE 'àé' AS accent,
       lower('ÀÉ') AS lower_acc, 'Straße' ILIKE 'STRASSE' AS eszett;

 zenkaku_ilike | lower_zen | zen_han | accent | lower_acc | eszett 
---------------+-----------+---------+--------+-----------+--------
 t             | ａｂｃ    | f       | t      | àé        | f

• 全角の ＡＢＣ と ａｂｃ は、ILIKEで一致します（C.UTF-8ロケールの場合）
• 全角 ＡＢＣ と半角 abc は一致しません。 全角と半角は別の文字です
• ドイツ語の ß と SS のように、文字数が変わる対応は一致しません

照合順序を "C" にすると、ASCIIの範囲だけが大文字小文字の変換対象になります。

SELECT lower('ＡＢＣ' COLLATE "C") AS c_lower,
       'ＡＢＣ' ILIKE 'ａｂｃ' COLLATE "C" AS c_ilike,
       'ÀÉ' ILIKE 'àé' COLLATE "C" AS c_acc;

 c_lower | c_ilike | c_acc 
---------+---------+-------
 ＡＢＣ  | f       | f

データベースを C ロケールで作っている場合、全角英字やアクセント付き文字はILIKEでもlower()でも区別されたままになります。「ILIKEなのに全角の大文字小文字が一致しない」ときは、まずロケールを確かめてください。

全角と半角も同じとして扱う2つの方法

1つめは、normalize() でNFKC正規化してから小文字にする方法です。

SELECT normalize('ＡＢＣ', NFKC) AS nfkc, lower(normalize('ＡＢＣ', NFKC)) = 'abc' AS eq;

 nfkc | eq 
------+----
 ABC  | t

2つめは、方式4の非決定的照合順序です。und-u-ks-level2 は全角と半角の違いも同じとして扱いました。

SELECT 'ＡＢＣ' = 'abc' COLLATE case_insensitive AS zen_han,
       'ｱ' = 'ア' COLLATE case_insensitive AS kana;

 zen_han | kana 
---------+------
 t       | t

半角カナと全角カナも等しいと判定されています。citextは全角の大文字小文字は同一視しますが、全角と半角は別のままです。

SELECT 'ＡＢＣ'::citext = 'ａｂｃ'::citext AS citext_zen,
       'ＡＢＣ'::citext = 'abc'::citext AS citext_zen_han;

 citext_zen | citext_zen_han 
------------+----------------
 t          | f

トルコ語のi

トルコ語では、大文字 I の小文字は点のない ı、小文字 i の大文字は点のある İ です。トルコ語の照合順序を指定すると、この規則で変換されます。

SELECT lower('I') AS def, lower('I' COLLATE "tr-x-icu") AS tr,
       upper('i' COLLATE "tr-x-icu") AS tr_up,
       'I' ILIKE 'i' AS def_ilike,
       'I' ILIKE 'i' COLLATE "tr-x-icu" AS tr_ilike,
       'I' ILIKE 'ı' COLLATE "tr-x-icu" AS tr_dotless,
       'İ' ILIKE 'i' COLLATE "tr-x-icu" AS tr_dotted;

 def | tr | tr_up | def_ilike | tr_ilike | tr_dotless | tr_dotted 
-----+----+-------+-----------+----------+------------+-----------
 i   | ı  | İ     | t         | f        | t          | t

トルコ語の照合順序では 'I' ILIKE 'i' が false になります。多言語のデータを扱うときは、「どのロケールのルールで区別なしにするか」を決めておくと、検索結果の説明がつきやすくなります。

PostgreSQL 18のcasefold()

18のリリースノートには、より細かな大文字小文字無視の比較のために casefold() 関数が加わったとあります。ß の例で確かめました。

SELECT casefold('Straße' COLLATE "und-x-icu") = casefold('STRASSE' COLLATE "und-x-icu") AS icu_eq,
       lower('Straße') = lower('STRASSE') AS lower_eq;

 icu_eq | lower_eq 
--------+----------
 t      | f

lower()では一致しない組み合わせが、ICU照合順序つきのcasefold()では一致しました（既定のC.UTF-8のままでは casefold('Straße') は straße を返し、一致しませんでした）。

大文字小文字を無視したユニーク制約の作り方

「Taro@example.com と TARO@EXAMPLE.COM を同じアドレスとして重複登録を防ぐ」方法を3つ示します。

lower()のユニーク式インデックス

CREATE TABLE accounts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL
);
CREATE UNIQUE INDEX accounts_email_lower_key ON accounts (lower(email));

INSERT INTO accounts (email) VALUES ('Taro@example.com');
INSERT INTO accounts (email) VALUES ('TARO@EXAMPLE.COM');

INSERT 0 1
ERROR:  duplicate key value violates unique constraint "accounts_email_lower_key"
DETAIL:  Key (lower(email))=(taro@example.com) already exists.

ON CONFLICT で使うときは、対象に同じ式を書きます。

INSERT INTO accounts (email) VALUES ('TARO@EXAMPLE.COM')
ON CONFLICT (lower(email)) DO NOTHING;

INSERT 0 0

このインデックスは WHERE lower(email) = lower($1) の検索にもそのまま使われるので、ログイン時の照合と重複防止を1つで兼ねられます。

citext列にUNIQUEを付ける

CREATE TABLE accounts2 (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email citext NOT NULL UNIQUE
);
INSERT INTO accounts2 (email) VALUES ('Taro@example.com');
INSERT INTO accounts2 (email) VALUES ('TARO@EXAMPLE.COM');

INSERT 0 1
ERROR:  duplicate key value violates unique constraint "accounts2_email_key"
DETAIL:  Key (email)=(TARO@EXAMPLE.COM) already exists.

非決定的照合順序の列にUNIQUEを付ける

CREATE TABLE accounts3 (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text COLLATE case_insensitive NOT NULL UNIQUE
);
INSERT INTO accounts3 (email) VALUES ('Taro@example.com');
INSERT INTO accounts3 (email) VALUES ('TARO@EXAMPLE.COM');

INSERT 0 1
ERROR:  duplicate key value violates unique constraint "accounts3_email_key"
DETAIL:  Key (email)=(TARO@EXAMPLE.COM) already exists.

つまずきやすい点と確認ポイント

よくある質問

Q. ILIKEとlower()のLIKEは、結果が同じになりますか？ A. 今回確かめた範囲（ASCII、全角英字、アクセント付き文字）では同じ結果でした。どちらもロケールに従うので、照合順序を明示した場合は同じ照合順序を両方に適用する必要があります。インデックスの使われ方は大きく違い、lower()は式インデックスで完全一致と前方一致が速くなります。

Q. 列そのものを、最初から大文字小文字を区別しない設定にできますか？ A. citext型にするか、非決定的照合順序を列に付けると、= での比較が区別なしになります。パターン検索（LIKE）まで含めて速くしたい場合は、pg_trgmのGINインデックスを併用します。

Q. pg_trgmのGINインデックスとlower()の式インデックス、両方作ってもよいですか？ A. 用途が分かれていれば併用できます。完全一致とユニーク制約はlower()の式インデックス、部分一致の検索はpg_trgm、という分担です。インデックスが増えるほど更新時の処理は増えるので、実際に使う検索に絞って作ります。

Q. ILIKEはSQL標準ですか？ A. PostgreSQLの拡張です（公式ドキュメントに明記）。ほかのデータベースへ移す可能性があるSQLでは、lower(col) LIKE lower(pattern) の形にしておくと移しやすくなります。

まとめ

• ILIKEはLIKEの大文字小文字を区別しない版。演算子表記は ~~*、否定は NOT ILIKE（!~~*）
• ILIKEは通常のB-treeインデックスを使わず、20万行で250〜330msのSeq Scanになった
• 完全一致・前方一致は lower(email) の式インデックス（text_pattern_ops）で0.1ms未満
• 中間一致はpg_trgmのGINインデックス。ILIKEのままで0.2msになり、検索語は3文字以上が目安
• citextと非決定的照合順序は = とユニーク制約を区別なしにする方式。非決定的照合順序のLIKEは18から、ILIKEはエラーになる
• 全角英字やトルコ語の i の扱いはロケールと照合順序で変わる。自分の環境で確かめておく
• % と _ を文字として探すときは \%、\_、または ESCAPE 句を使う

参考・出典

• PostgreSQL 18 Documentation「9.7. Pattern Matching」 https://www.postgresql.org/docs/18/functions-matching.html
• PostgreSQL 18 Documentation「pg_trgm」 https://www.postgresql.org/docs/18/pgtrgm.html
• PostgreSQL 18 Documentation「citext」 https://www.postgresql.org/docs/18/citext.html
• PostgreSQL 18 Documentation「23.2. Collation Support」 https://www.postgresql.org/docs/18/collation.html
• PostgreSQL 18 Release Notes https://www.postgresql.org/docs/release/18.0/

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。