はじめに

自分用のメモです。PostgreSQLで正規表現（Regex）を使ってデータを検索する際、演算子の使い方やアンカーの指定をすぐ忘れてしまうので整理しておきます。

部分一致

PostgreSQLで正規表現マッチングを行う際のメイン演算子は ~ です。基本的には「このパターンが文字列のどこかに含まれているか？」という部分一致として機能します。

大文字小文字を区別する: ~
大文字小文字を区別しない: ~*

-- メールアドレスに "gmail" を含むユーザーを検索
SELECT * FROM users WHERE email ~ 'gmail';

-- 大文字小文字を無視して "tokyo" または "osaka" を含む住所を検索
SELECT * FROM addresses WHERE city ~* 'tokyo|osaka';

完全一致

文字列全体が指定したパターンと完全に一致しているかを確認したい場合は、正規表現のアンカーである ^（先頭）と $（末尾）を使います。

-- "A" で始まり、数字が3桁続き、"Z" で終わるコードと完全に一致（例: A123Z）
SELECT * FROM products WHERE code ~ '^A[0-9]{3}Z$';

-- ちょうど "admin" という文字列と完全に一致するユーザー名を探す
SELECT * FROM users WHERE username ~ '^admin$';

否定

特定のパターンを「含まない」データを探す場合は、~ の前に ! を付けます。

指定パターンが含まれない（区別あり）: !~
指定パターンが含まれない（区別なし）: !~*

-- "temp_" で始まらないテーブル名を探す
SELECT table_name FROM information_schema.tables WHERE table_name !~ '^temp_';

-- "test" という文字列を含まないログメッセージを検索
SELECT * FROM logs WHERE message !~* 'test';

環境

PostgreSQL 18