はじめに
自分用のメモです。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