PostgreSQL 大文字小文字区別の完全ガイド：検索とデータ処理を正確に行う方法

データベース・SQL

「テーブル名が見つからないエラーが出る…」 「検索で大文字小文字を無視したいけど、どうすればいい？」 「他のDBから移行したら、大文字小文字の扱いが違って困った」

PostgreSQLを使っていて、こんな問題に遭遇したことはありませんか？

PostgreSQLの大文字小文字の扱いは、他のデータベースシステムと異なる部分があり、適切に理解していないと思わぬエラーやパフォーマンス問題につながります。特に、識別子（テーブル名やカラム名）とデータ（文字列値）では扱いが全く異なります。

この記事では、PostgreSQLにおける大文字小文字の扱いを完全に理解し、効率的な検索とデータ処理を実現する方法を、実例とベストプラクティスを交えながら徹底的に解説していきます。

スポンサーリンク

🔤 識別子での大文字小文字

基本ルール：自動的な小文字変換

クォートなし識別子の動作

PostgreSQLの基本動作：

-- クォートなしの識別子は自動的に小文字に変換される
CREATE TABLE UserProfile (
    UserID INTEGER,
    UserName VARCHAR(50)
);

-- 実際に作成されるテーブル
\d
-- テーブル名: userprofile
-- カラム名: userid, username (すべて小文字！)

-- これらはすべて同じテーブルを参照
SELECT * FROM UserProfile;    -- OK
SELECT * FROM userprofile;    -- OK
SELECT * FROM USERPROFILE;    -- OK
SELECT * FROM uSeRpRoFiLe;    -- OK

ダブルクォートで大文字小文字を保持

厳密な識別子の指定

-- ダブルクォートで囲むと大文字小文字が保持される
CREATE TABLE "UserProfile" (
    "UserID" INTEGER,
    "UserName" VARCHAR(50)
);

-- アクセス時も正確に指定する必要がある
SELECT * FROM "UserProfile";    -- OK
SELECT * FROM UserProfile;      -- ERROR! (userprofileテーブルを探す)
SELECT * FROM userprofile;      -- ERROR!
SELECT * FROM "userprofile";    -- ERROR!

-- カラム名も同様
SELECT "UserID", "UserName" FROM "UserProfile";  -- OK
SELECT UserID FROM "UserProfile";                -- ERROR!

混在環境での注意点

異なる記法が混在する場合

-- 危険な例：混在する定義
CREATE TABLE "Product" (         -- 大文字のP
    product_id SERIAL,           -- 小文字（実際は小文字で保存）
    "ProductName" VARCHAR(100),  -- 大文字小文字混在
    PRICE DECIMAL(10,2)          -- 大文字（実際は小文字で保存）
);

-- アクセス時の混乱
SELECT product_id FROM "Product";      -- OK
SELECT "product_id" FROM "Product";    -- ERROR!
SELECT "ProductName" FROM "Product";   -- OK
SELECT ProductName FROM "Product";     -- ERROR!
SELECT price FROM "Product";           -- OK
SELECT PRICE FROM "Product";           -- OK
SELECT "PRICE" FROM "Product";         -- ERROR!

🔍 データ（文字列）での大文字小文字

デフォルトは大文字小文字を区別

文字列比較の基本動作

-- テストテーブルの作成
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

INSERT INTO users (username, email) VALUES 
    ('JohnDoe', 'john@example.com'),
    ('johndoe', 'johndoe@example.com'),
    ('JOHNDOE', 'JOHNDOE@example.com'),
    ('johnDOE', 'john.doe@example.com');

-- 大文字小文字を区別する（デフォルト）
SELECT * FROM users WHERE username = 'JohnDoe';
-- 結果：1件のみ（'JohnDoe'）

SELECT * FROM users WHERE username = 'johndoe';
-- 結果：1件のみ（'johndoe'）

-- 部分一致でも区別される
SELECT * FROM users WHERE username LIKE '%john%';
-- 結果：1件のみ（'johndoe'）

大文字小文字を無視する方法

方法1：LOWER/UPPER関数

-- LOWER関数で統一して比較
SELECT * FROM users 
WHERE LOWER(username) = LOWER('JohnDoe');
-- 結果：4件すべて

-- UPPER関数でも同様
SELECT * FROM users 
WHERE UPPER(username) = UPPER('JohnDoe');
-- 結果：4件すべて

-- 部分一致検索
SELECT * FROM users 
WHERE LOWER(username) LIKE LOWER('%JOHN%');
-- 結果：4件すべて

方法2：ILIKE演算子

-- ILIKEは大文字小文字を区別しない
SELECT * FROM users 
WHERE username ILIKE 'johndoe';
-- 結果：4件すべて

-- ワイルドカードとの組み合わせ
SELECT * FROM users 
WHERE username ILIKE '%john%';
-- 結果：4件すべて

-- 前方一致
SELECT * FROM users 
WHERE email ILIKE 'john%';
-- 結果：3件（john@, johndoe@, john.doe@）

方法3：正規表現（~*演算子）

-- ~* は大文字小文字を区別しない正規表現マッチ
SELECT * FROM users 
WHERE username ~* '^john';
-- 結果：4件すべて（johnで始まる）

-- ~ は大文字小文字を区別する
SELECT * FROM users 
WHERE username ~ '^john';
-- 結果：2件（'johndoe', 'johnDOE'）

-- より複雑なパターン
SELECT * FROM users 
WHERE username ~* '^j.*doe$';
-- 結果：4件すべて

📊 照合順序（Collation）の活用

データベースレベルの設定

照合順序の確認と設定

-- 現在のデータベースの照合順序を確認
SELECT datname, datcollate, datctype 
FROM pg_database 
WHERE datname = current_database();

-- 新しいデータベースを特定の照合順序で作成
CREATE DATABASE mydb_ci 
WITH 
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8';

-- ICU照合順序（PostgreSQL 10以降）
CREATE DATABASE mydb_icu
WITH 
    ENCODING = 'UTF8'
    ICU_LOCALE = 'und-u-ks-level2'  -- 大文字小文字を区別しない
    TEMPLATE = template0;

カラムレベルの照合順序

PostgreSQL 12以降の非決定的照合順序

-- 大文字小文字を区別しない照合順序を作成
CREATE COLLATION case_insensitive (
    provider = icu,
    locale = 'und-u-ks-level2',
    deterministic = false
);

-- テーブル作成時に照合順序を指定
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) COLLATE case_insensitive,
    description TEXT
);

-- データ挿入
INSERT INTO products (name, description) VALUES 
    ('iPhone', 'Smartphone by Apple'),
    ('IPHONE', 'Another entry'),
    ('iphone', 'Yet another entry');

-- 照合順序により大文字小文字を区別しない
SELECT * FROM products WHERE name = 'iPhone';
-- 結果：3件すべて（照合順序の効果）

-- ユニーク制約も大文字小文字を区別しない
CREATE UNIQUE INDEX idx_products_name ON products(name);
-- ERROR: duplicate key value violates unique constraint

🚀 インデックスと検索パフォーマンス

関数インデックスの活用

LOWER/UPPERインデックス

-- 大量データでのテスト
CREATE TABLE large_users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 100万件のデータ投入
INSERT INTO large_users (username, email)
SELECT 
    'User' || CASE WHEN random() > 0.5 THEN UPPER(md5(i::TEXT)) ELSE LOWER(md5(i::TEXT)) END,
    LOWER(md5(random()::TEXT)) || '@example.com'
FROM generate_series(1, 1000000) i;

-- 通常のインデックス（大文字小文字を区別）
CREATE INDEX idx_username ON large_users(username);

-- LOWER関数でのクエリは遅い
EXPLAIN ANALYZE
SELECT * FROM large_users WHERE LOWER(username) = LOWER('User123ABC');
-- Seq Scan (実行時間: 250ms)

-- 関数インデックスを作成
CREATE INDEX idx_username_lower ON large_users(LOWER(username));

-- 同じクエリが高速化
EXPLAIN ANALYZE
SELECT * FROM large_users WHERE LOWER(username) = LOWER('User123ABC');
-- Index Scan (実行時間: 0.5ms)

パターンマッチングの最適化

text_pattern_opsインデックス

-- LIKE検索用の特別なインデックス
CREATE INDEX idx_username_pattern ON large_users(username text_pattern_ops);

-- 前方一致検索が高速
EXPLAIN ANALYZE
SELECT * FROM large_users WHERE username LIKE 'User123%';
-- Index Scan

-- 大文字小文字を区別しない前方一致
CREATE INDEX idx_username_lower_pattern 
ON large_users(LOWER(username) text_pattern_ops);

EXPLAIN ANALYZE
SELECT * FROM large_users 
WHERE LOWER(username) LIKE LOWER('User123%');
-- Index Scan

トリグラムインデックス

pg_trgmで柔軟な検索

-- 拡張機能を有効化
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- トリグラムインデックス
CREATE INDEX idx_username_trgm ON large_users 
USING gin(username gin_trgm_ops);

-- 部分一致でも高速（大文字小文字を区別）
EXPLAIN ANALYZE
SELECT * FROM large_users WHERE username LIKE '%ABC%';
-- Bitmap Index Scan

-- ILIKEでも使用可能（大文字小文字を区別しない）
CREATE INDEX idx_username_lower_trgm ON large_users 
USING gin(LOWER(username) gin_trgm_ops);

EXPLAIN ANALYZE
SELECT * FROM large_users WHERE username ILIKE '%abc%';
-- Bitmap Index Scan

💡 実践的な検索実装

汎用検索関数の作成

大文字小文字を考慮した検索

-- 柔軟な検索関数
CREATE OR REPLACE FUNCTION search_users(
    p_search_term TEXT,
    p_case_sensitive BOOLEAN DEFAULT FALSE,
    p_exact_match BOOLEAN DEFAULT FALSE
)
RETURNS TABLE(
    user_id INTEGER,
    username VARCHAR,
    email VARCHAR,
    match_type TEXT
) AS $$
BEGIN
    IF p_exact_match THEN
        IF p_case_sensitive THEN
            -- 完全一致（大文字小文字を区別）
            RETURN QUERY
            SELECT id, u.username, u.email, 'exact'::TEXT
            FROM large_users u
            WHERE u.username = p_search_term;
        ELSE
            -- 完全一致（大文字小文字を区別しない）
            RETURN QUERY
            SELECT id, u.username, u.email, 'exact_ci'::TEXT
            FROM large_users u
            WHERE LOWER(u.username) = LOWER(p_search_term);
        END IF;
    ELSE
        IF p_case_sensitive THEN
            -- 部分一致（大文字小文字を区別）
            RETURN QUERY
            SELECT id, u.username, u.email, 'partial'::TEXT
            FROM large_users u
            WHERE u.username LIKE '%' || p_search_term || '%';
        ELSE
            -- 部分一致（大文字小文字を区別しない）
            RETURN QUERY
            SELECT id, u.username, u.email, 'partial_ci'::TEXT
            FROM large_users u
            WHERE u.username ILIKE '%' || p_search_term || '%';
        END IF;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 使用例
SELECT * FROM search_users('john', FALSE, FALSE);  -- 大文字小文字無視、部分一致
SELECT * FROM search_users('John', TRUE, TRUE);    -- 大文字小文字区別、完全一致

全文検索での大文字小文字

to_tsvectorとto_tsquery

-- 全文検索用のカラムとインデックス
ALTER TABLE large_users ADD COLUMN search_vector tsvector;

UPDATE large_users 
SET search_vector = to_tsvector('english', 
    COALESCE(username, '') || ' ' || COALESCE(email, ''));

CREATE INDEX idx_search_vector ON large_users USING gin(search_vector);

-- 全文検索（デフォルトで大文字小文字を区別しない）
SELECT * FROM large_users 
WHERE search_vector @@ to_tsquery('english', 'JOHN | john');
-- 大文字でも小文字でも同じ結果

-- より高度な検索
SELECT 
    id,
    username,
    ts_rank(search_vector, query) as rank
FROM large_users,
    to_tsquery('english', 'user & abc') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

🔄 他DBからの移行時の注意点

MySQLからの移行

-- MySQLの動作を模倣する設定

-- 方法1：citext拡張を使用
CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE mysql_compatible (
    id SERIAL PRIMARY KEY,
    username CITEXT,  -- 大文字小文字を区別しない型
    email CITEXT
);

INSERT INTO mysql_compatible (username, email) VALUES 
    ('JohnDoe', 'john@example.com'),
    ('johndoe', 'johndoe@example.com');  -- 重複エラー（UNIQUE制約がある場合）

-- 方法2：トリガーで小文字に統一
CREATE OR REPLACE FUNCTION lowercase_username()
RETURNS TRIGGER AS $$
BEGIN
    NEW.username := LOWER(NEW.username);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_lowercase_username
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION lowercase_username();

SQL Serverからの移行

-- SQL Serverのような大文字識別子を保持

-- 移行スクリプトの例
CREATE OR REPLACE FUNCTION migrate_from_sqlserver(
    p_table_def TEXT
)
RETURNS TEXT AS $$
DECLARE
    v_result TEXT;
BEGIN
    -- 大文字の識別子をダブルクォートで囲む
    v_result := regexp_replace(
        p_table_def,
        '\b([A-Z][A-Za-z0-9_]*)\b',
        '"\1"',
        'g'
    );
    
    RETURN v_result;
END;
$$ LANGUAGE plpgsql;

-- 使用例
SELECT migrate_from_sqlserver('
    CREATE TABLE UserProfile (
        UserID INT PRIMARY KEY,
        UserName VARCHAR(50)
    )
');
-- 結果：CREATE TABLE "UserProfile" ("UserID" INT PRIMARY KEY, "UserName" VARCHAR(50))

⚠️ よくある問題と解決策

問題1：テーブルが見つからない

-- よくあるエラー
CREATE TABLE "Users" (id INT);
SELECT * FROM Users;  -- ERROR: relation "users" does not exist

-- 解決方法
-- 1. 正しくクォートする
SELECT * FROM "Users";

-- 2. 情報スキーマで確認
SELECT table_name, table_schema
FROM information_schema.tables
WHERE LOWER(table_name) LIKE '%user%'
   OR table_name LIKE '%user%';

問題2：パフォーマンスの低下

-- 問題：LOWER関数でインデックスが使えない
EXPLAIN SELECT * FROM large_users WHERE LOWER(username) = 'john';
-- Seq Scan

-- 解決：関数インデックスまたは生成カラム
-- PostgreSQL 12以降
ALTER TABLE large_users 
ADD COLUMN username_lower VARCHAR(50) 
GENERATED ALWAYS AS (LOWER(username)) STORED;

CREATE INDEX idx_username_lower_gen ON large_users(username_lower);

-- 高速検索
SELECT * FROM large_users WHERE username_lower = 'john';

問題3：ユニーク制約の問題

-- 問題：大文字小文字違いで重複
CREATE TABLE users_unique (
    username VARCHAR(50) UNIQUE
);

INSERT INTO users_unique VALUES ('john');
INSERT INTO users_unique VALUES ('JOHN');  -- 成功してしまう

-- 解決：関数インデックスでユニーク制約
CREATE UNIQUE INDEX idx_username_unique_ci 
ON users_unique(LOWER(username));

-- これで重複を防げる
INSERT INTO users_unique VALUES ('John');  -- ERROR

📈 パフォーマンス比較

各手法のベンチマーク

-- テストデータ準備（100万件）
CREATE TABLE perf_test AS
SELECT 
    id,
    'User_' || md5(random()::TEXT) as username
FROM generate_series(1, 1000000) id;

-- 各種インデックス
CREATE INDEX idx_normal ON perf_test(username);
CREATE INDEX idx_lower ON perf_test(LOWER(username));
CREATE INDEX idx_pattern ON perf_test(username text_pattern_ops);

-- ベンチマーク実行
\timing on

-- 1. 通常の比較（0.5ms）
SELECT * FROM perf_test WHERE username = 'User_abc123';

-- 2. LOWER関数（インデックスなし：250ms）
SELECT * FROM perf_test WHERE LOWER(username) = LOWER('User_ABC123');

-- 3. LOWER関数（インデックスあり：0.5ms）
SELECT * FROM perf_test WHERE LOWER(username) = LOWER('User_ABC123');

-- 4. ILIKE（インデックスなし：300ms）
SELECT * FROM perf_test WHERE username ILIKE 'User_ABC123';

-- 5. citext型（0.5ms）
ALTER TABLE perf_test ALTER COLUMN username TYPE citext;
SELECT * FROM perf_test WHERE username = 'User_ABC123';

💡 ベストプラクティス

設計指針

-- 1. 識別子は小文字で統一
CREATE TABLE user_profiles (  -- snake_case推奨
    user_id INTEGER,
    user_name VARCHAR(50)
);

-- 2. 大文字小文字を区別しない検索が必要な場合
-- Option A: citext型を使用
CREATE TABLE users_citext (
    username CITEXT PRIMARY KEY
);

-- Option B: 生成カラムとインデックス
CREATE TABLE users_generated (
    username VARCHAR(50),
    username_lower VARCHAR(50) GENERATED ALWAYS AS (LOWER(username)) STORED
);

-- 3. アプリケーション層での統一
-- 入力時に小文字に変換してから保存

検索実装のガイドライン

-- 汎用的な検索ストアドプロシージャ
CREATE OR REPLACE FUNCTION smart_search(
    p_table_name TEXT,
    p_column_name TEXT,
    p_search_term TEXT,
    p_options JSONB DEFAULT '{}'
)
RETURNS SETOF RECORD AS $$
DECLARE
    v_case_sensitive BOOLEAN := COALESCE((p_options->>'case_sensitive')::BOOLEAN, FALSE);
    v_exact_match BOOLEAN := COALESCE((p_options->>'exact_match')::BOOLEAN, FALSE);
    v_use_index BOOLEAN := COALESCE((p_options->>'use_index')::BOOLEAN, TRUE);
    v_query TEXT;
BEGIN
    v_query := format(
        'SELECT * FROM %I WHERE %s',
        p_table_name,
        CASE 
            WHEN v_exact_match AND v_case_sensitive THEN
                format('%I = %L', p_column_name, p_search_term)
            WHEN v_exact_match AND NOT v_case_sensitive THEN
                format('LOWER(%I) = LOWER(%L)', p_column_name, p_search_term)
            WHEN NOT v_exact_match AND v_case_sensitive THEN
                format('%I LIKE %L', p_column_name, '%' || p_search_term || '%')
            ELSE
                format('%I ILIKE %L', p_column_name, '%' || p_search_term || '%')
        END
    );
    
    RETURN QUERY EXECUTE v_query;
END;
$$ LANGUAGE plpgsql;

📚 まとめ：PostgreSQLの大文字小文字を完全マスター

PostgreSQLでの大文字小文字の扱いのポイント：

✅ 識別子は自動的に小文字に変換される（クォートなしの場合）
✅ 文字列データはデフォルトで大文字小文字を区別
✅ ILIKE、LOWER/UPPER、citext型で大文字小文字を無視
✅ 関数インデックスで検索パフォーマンスを最適化
✅ 照合順序（Collation）で柔軟な制御が可能

これらの知識を活用することで、正確で高速な検索処理を実装できます。

今すぐ実践すべき3つのアクション：

  1. 既存の識別子をすべて小文字に統一する
  2. 大文字小文字を無視する検索には関数インデックスを作成
  3. citext型やILIKEを適切に使い分ける

PostgreSQLの大文字小文字の仕組みを理解して、効率的なデータベース設計を実現しましょう！

Youtube

神話・歴史・伝承の解説チャンネル

スポンサーリンク

コメント

タイトルとURLをコピーしました