PostgreSQL文字列結合完全マスター！||演算子からstring_aggまで全パターン解説

「NULLが混ざると結果もNULLになってしまう…」 「複数行を1つの文字列にまとめたい」 「CSVやJSON形式で出力したい」

PostgreSQLの文字列結合は、 単純に見えて意外と奥が深いんです。

||演算子、CONCAT、string_agg… それぞれ使いどころが違います。

この記事を読めば、 フルネーム生成からCSV出力まで、 あらゆる文字列結合をマスターできます！

---

基本的な文字列結合

||演算子（パイプ演算子）

-- 最も基本的な文字列結合
SELECT 'Hello' || ' ' || 'World' AS greeting;
-- 結果：Hello World

-- カラムの結合
SELECT 
    first_name || ' ' || last_name AS full_name,
    '〒' || postal_code || ' ' || address AS full_address
FROM users;

-- 複数の||演算子を連鎖
SELECT 
    department || ' - ' || position || ' (' || employee_code || ')' AS title
FROM employees;
-- 結果：営業部 - 課長 (EMP001)

-- ⚠️ 注意：NULLが含まれると結果もNULL
SELECT 'Hello' || NULL || 'World';  -- NULL

CONCAT関数

-- NULL安全な結合（NULLは空文字として扱われる）
SELECT CONCAT('Hello', NULL, 'World') AS result;
-- 結果：HelloWorld

-- 複数の引数を結合
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM users;

-- 数値も自動的に文字列に変換
SELECT CONCAT('ID: ', user_id, ' - Score: ', score) AS info
FROM scores;

-- ネストして使用
SELECT CONCAT(
    CONCAT('【', category, '】'),
    title
) AS formatted_title
FROM articles;

CONCAT_WS関数（区切り文字付き結合）

-- CONCAT_WS = CONCAT With Separator
-- 第1引数が区切り文字

-- 基本的な使い方
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name
FROM users;
-- NULL値は無視される：John NULL Smith → John Smith

-- カンマ区切りでCSV風に
SELECT CONCAT_WS(',', 
    employee_id, 
    name, 
    department, 
    CAST(salary AS TEXT)
) AS csv_line
FROM employees;

-- 住所の結合（NULLを考慮）
SELECT CONCAT_WS(' ', 
    postal_code,
    prefecture,
    city,
    street,
    building
) AS full_address
FROM addresses;
-- building がNULLでも正常に動作

-- 階層パスの作成
SELECT CONCAT_WS(' > ', 
    category1,
    category2,
    category3
) AS breadcrumb
FROM products;
-- 結果：家電 > テレビ > 液晶テレビ

---

NULL値を考慮した結合

COALESCEを使った安全な結合

-- NULLを別の値に置換してから結合
SELECT 
    COALESCE(first_name, '') || ' ' || COALESCE(last_name, '') AS full_name
FROM users;

-- デフォルト値を設定
SELECT 
    COALESCE(title, '無題') || ' by ' || COALESCE(author, '不明') AS book_info
FROM books;

-- 条件付き結合
SELECT 
    name || 
    CASE 
        WHEN phone IS NOT NULL THEN ' (Tel: ' || phone || ')'
        ELSE ''
    END AS contact_info
FROM customers;

NULLIFとの組み合わせ

-- 空文字をNULLに変換してから処理
SELECT 
    CONCAT_WS(' ', 
        NULLIF(TRIM(first_name), ''),
        NULLIF(TRIM(middle_name), ''),
        NULLIF(TRIM(last_name), '')
    ) AS clean_name
FROM users;

-- 0を表示しない
SELECT 
    product_name || 
    CASE 
        WHEN stock > 0 THEN ' (在庫: ' || stock || ')'
        ELSE ' (在庫なし)'
    END AS product_info
FROM products;

---

集約関数での文字列結合

STRING_AGG関数（複数行を1つに）

-- 基本的な使い方
SELECT STRING_AGG(name, ', ') AS all_names
FROM users;
-- 結果：田中, 鈴木, 佐藤

-- ORDER BY付きで並び順を指定
SELECT 
    department,
    STRING_AGG(name, ', ' ORDER BY employee_id) AS members
FROM employees
GROUP BY department;

-- DISTINCT で重複除去
SELECT STRING_AGG(DISTINCT category, ' / ' ORDER BY category) AS categories
FROM products;

-- グループごとに結合
SELECT 
    department,
    STRING_AGG(
        name || '(' || position || ')', 
        ', ' 
        ORDER BY hire_date
    ) AS member_list
FROM employees
GROUP BY department;
-- 結果：営業部 | 田中(部長), 鈴木(課長), 山田(主任)

-- 条件付きで集約
SELECT 
    project_id,
    STRING_AGG(
        CASE 
            WHEN role = 'lead' THEN '★' || name
            ELSE name
        END,
        ', '
        ORDER BY role, name
    ) AS team_members
FROM project_members
GROUP BY project_id;

ARRAY_TO_STRING（配列を文字列に）

-- 配列を文字列に変換
SELECT ARRAY_TO_STRING(ARRAY['A', 'B', 'C'], ', ') AS result;
-- 結果：A, B, C

-- NULLを含む配列の処理
SELECT ARRAY_TO_STRING(
    ARRAY['A', NULL, 'B', 'C'], 
    ', ',
    'N/A'  -- NULL置換文字
) AS result;
-- 結果：A, N/A, B, C

-- サブクエリの結果を配列にして結合
SELECT 
    u.user_id,
    u.name,
    ARRAY_TO_STRING(
        ARRAY(
            SELECT skill_name 
            FROM user_skills 
            WHERE user_id = u.user_id 
            ORDER BY skill_level DESC
        ),
        ' / '
    ) AS skills
FROM users u;

---

フォーマット関数

FORMAT関数

-- sprintf スタイルのフォーマット
SELECT FORMAT('Hello, %s!', 'World');
-- 結果：Hello, World!

-- 複数のプレースホルダー
SELECT FORMAT(
    '%s年%s月%s日 %s時%s分',
    EXTRACT(YEAR FROM NOW()),
    EXTRACT(MONTH FROM NOW()),
    EXTRACT(DAY FROM NOW()),
    EXTRACT(HOUR FROM NOW()),
    EXTRACT(MINUTE FROM NOW())
) AS formatted_date;

-- 型指定フォーマット
SELECT FORMAT(
    'ID: %s, Score: %s点 (%.2f%%)',
    user_id,
    score,
    score * 100.0 / total_score
) AS result
FROM exam_results;

-- SQL識別子のエスケープ
SELECT FORMAT('SELECT * FROM %I WHERE %I = %L', 
    'users',           -- %I: 識別子（テーブル名、カラム名）
    'email',          -- %I: 識別子
    'test@example.com' -- %L: リテラル値
) AS dynamic_sql;

TO_CHAR関数（数値・日付のフォーマット）

-- 数値のフォーマット
SELECT 
    TO_CHAR(1234567.89, '9,999,999.99') AS formatted_number,
    TO_CHAR(1234567.89, 'FM9,999,999.00') AS no_space,
    TO_CHAR(0.75, 'FM990.0%') AS percentage;

-- 日付のフォーマット
SELECT 
    TO_CHAR(NOW(), 'YYYY年MM月DD日 HH24時MI分SS秒') AS japanese_date,
    TO_CHAR(NOW(), 'Day, DD Mon YYYY') AS english_date,
    TO_CHAR(NOW(), 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS iso_date;

-- 金額表示
SELECT 
    '¥' || TO_CHAR(price, 'FM999,999,999') AS price_display
FROM products;

---

実践的な使用例

フルネーム・住所の生成

-- 日本式の名前結合
CREATE FUNCTION format_japanese_name(
    last_name TEXT,
    first_name TEXT,
    last_kana TEXT DEFAULT NULL,
    first_kana TEXT DEFAULT NULL
) RETURNS TEXT AS $$
BEGIN
    RETURN CONCAT(
        COALESCE(last_name, ''),
        CASE 
            WHEN last_name IS NOT NULL AND first_name IS NOT NULL THEN ' '
            ELSE ''
        END,
        COALESCE(first_name, ''),
        CASE 
            WHEN last_kana IS NOT NULL OR first_kana IS NOT NULL THEN 
                ' (' || CONCAT_WS(' ', last_kana, first_kana) || ')'
            ELSE ''
        END
    );
END;
$$ LANGUAGE plpgsql;

-- 使用例
SELECT format_japanese_name('山田', '太郎', 'ヤマダ', 'タロウ');
-- 結果：山田 太郎 (ヤマダ タロウ)

CSV/TSV形式での出力

-- CSV形式で出力
COPY (
    SELECT 
        CONCAT_WS(',',
            '"' || REPLACE(name, '"', '""') || '"',
            '"' || REPLACE(email, '"', '""') || '"',
            CAST(age AS TEXT),
            TO_CHAR(created_at, 'YYYY-MM-DD')
        ) AS csv_line
    FROM users
) TO STDOUT;

-- より安全なCSV生成関数
CREATE FUNCTION to_csv(VARIADIC fields TEXT[])
RETURNS TEXT AS $$
    SELECT STRING_AGG(
        CASE 
            WHEN f ~ '[,"\r\n]' THEN '"' || REPLACE(f, '"', '""') || '"'
            ELSE f
        END,
        ','
    )
    FROM UNNEST(fields) AS f;
$$ LANGUAGE sql IMMUTABLE;

-- 使用例
SELECT to_csv(name, email, phone::TEXT) FROM users;

JSON文字列の構築

-- 手動でJSON構築（非推奨だが理解のため）
SELECT 
    '{"name":"' || REPLACE(name, '"', '\"') || 
    '","age":' || age || 
    ',"active":' || active || '}' AS json_string
FROM users;

-- 推奨：JSON関数を使用
SELECT 
    json_build_object(
        'name', name,
        'age', age,
        'active', active
    )::TEXT AS json_string
FROM users;

-- 複数行をJSON配列に
SELECT 
    '[' || STRING_AGG(
        json_build_object(
            'id', id,
            'name', name
        )::TEXT,
        ','
    ) || ']' AS json_array
FROM users;

HTMLタグの生成

-- リンクタグの生成
SELECT 
    '<a href="/user/' || user_id || '">' || 
    REPLACE(REPLACE(name, '<', '&lt;'), '>', '&gt;') || 
    '</a>' AS html_link
FROM users;

-- テーブル行の生成
SELECT 
    '<tr>' ||
    '<td>' || id || '</td>' ||
    '<td>' || COALESCE(name, '') || '</td>' ||
    '<td>' || COALESCE(email, '') || '</td>' ||
    '</tr>' AS table_row
FROM users;

-- より安全なHTML生成関数
CREATE FUNCTION html_escape(input TEXT)
RETURNS TEXT AS $$
    SELECT REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(input, '&', '&amp;'),
                    '<', '&lt;'
                ),
                '>', '&gt;'
            ),
            '"', '&quot;'
        ),
        '''', '&#39;'
    );
$$ LANGUAGE sql IMMUTABLE;

ログメッセージの生成

-- 構造化ログメッセージ
CREATE FUNCTION create_log_message(
    level TEXT,
    module TEXT,
    message TEXT,
    user_id INT DEFAULT NULL
) RETURNS TEXT AS $$
BEGIN
    RETURN FORMAT(
        '[%s] [%s] [%s]%s %s',
        TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS.MS'),
        UPPER(level),
        module,
        CASE 
            WHEN user_id IS NOT NULL THEN ' [User:' || user_id || ']'
            ELSE ''
        END,
        message
    );
END;
$$ LANGUAGE plpgsql;

-- 使用例
SELECT create_log_message('error', 'auth', 'Login failed', 123);
-- [2024-01-15 10:30:45.123] [ERROR] [auth] [User:123] Login failed

---

パフォーマンス最適化

文字列結合のパフォーマンス比較

-- パフォーマンステスト
EXPLAIN ANALYZE
SELECT 
    -- 方法1：||演算子
    first_name || ' ' || last_name,
    -- 方法2：CONCAT
    CONCAT(first_name, ' ', last_name),
    -- 方法3：FORMAT
    FORMAT('%s %s', first_name, last_name)
FROM users
LIMIT 10000;

-- 一般的に：|| > CONCAT > FORMAT の順で高速

インデックスの考慮

-- 結合結果にインデックスを作成
CREATE INDEX idx_full_name ON users ((first_name || ' ' || last_name));

-- 式インデックスを使った検索
SELECT * FROM users 
WHERE (first_name || ' ' || last_name) = 'John Smith';

-- 生成列を使用（PostgreSQL 12+）
ALTER TABLE users 
ADD COLUMN full_name TEXT 
GENERATED ALWAYS AS (CONCAT_WS(' ', first_name, last_name)) STORED;

CREATE INDEX idx_generated_full_name ON users(full_name);

---

よくあるエラーと対処法

エラー1：NULLによる結果消失

-- ❌ 問題：NULLで全体がNULLに
SELECT first_name || ' ' || middle_name || ' ' || last_name FROM users;

-- ✅ 解決1：COALESCE使用
SELECT COALESCE(first_name, '') || ' ' || 
       COALESCE(middle_name, '') || ' ' || 
       COALESCE(last_name, '') FROM users;

-- ✅ 解決2：CONCAT_WS使用
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) FROM users;

エラー2：型の不一致

-- ❌ 問題：数値を直接結合
SELECT 'ID: ' || id FROM users;  -- エラーになる場合がある

-- ✅ 解決：明示的な型変換
SELECT 'ID: ' || id::TEXT FROM users;
SELECT 'ID: ' || CAST(id AS TEXT) FROM users;
SELECT CONCAT('ID: ', id) FROM users;  -- CONCATは自動変換

エラー3：特殊文字のエスケープ

-- ❌ 問題：引用符でエラー
SELECT 'It''s a nice day';  -- シングルクォートは2つ重ねる

-- ✅ 解決：$$記法を使用
SELECT $$It's a "nice" day$$;

-- ✅ 解決：FORMAT関数でエスケープ
SELECT FORMAT('It%Ls a "nice" day', '''');

---

まとめ：用途別推奨方法

単純な結合：

SELECT col1 || ' ' || col2 FROM table;

NULL安全な結合：

SELECT CONCAT(col1, ' ', col2) FROM table;
SELECT CONCAT_WS(' ', col1, col2, col3) FROM table;

複数行の集約：

SELECT STRING_AGG(col, ', ' ORDER BY id) FROM table;

フォーマット付き出力：

SELECT FORMAT('Name: %s, Age: %s', name, age) FROM table;

配列の結合：

SELECT ARRAY_TO_STRING(array_col, ', ') FROM table;

---

文字列結合をマスターすれば、 データの加工と出力が自由自在になります！

用途に応じて最適な方法を選んで、 効率的なクエリを書いていきましょう。

---

文字列結合で困ったことがあれば、ぜひ質問してください。実践的な解決方法を一緒に考えましょう！