PostgreSQL 0埋め完全ガイド – 数値を美しく整形する全テクニック

「社員番号を001, 002, 003…と表示したい」

「請求書番号を8桁固定（00001234）にしたい」

「郵便番号や電話番号の桁数を揃えたい」

「CSVエクスポート時に桁数を統一したい」

こんな要望を叶えるのが**0埋め（ゼロパディング）**です！

0埋めは、数値の左側に0を追加して桁数を揃える処理。見た目が整うだけでなく、ソート順の制御やシステム連携でも重要な役割を果たします。

この記事では、PostgreSQLで0埋めを行う様々な方法と、実務で使える実践的なテクニックを詳しく解説していきます！

0埋めの基本 – LPAD関数

LPAD関数の使い方

-- 基本構文
LPAD(文字列, 長さ, 埋め文字)

-- 数値を5桁の0埋め
SELECT LPAD(123::TEXT, 5, '0');  -- 結果: '00123'
SELECT LPAD('7', 5, '0');        -- 結果: '00007'
SELECT LPAD('999', 5, '0');      -- 結果: '00999'

-- 数値型カラムの0埋め
SELECT 
    id,
    LPAD(id::TEXT, 6, '0') AS id_padded
FROM employees;

-- 結果例：
-- id  | id_padded
-- 1   | 000001
-- 42  | 000042
-- 999 | 000999

実践的な使用例

-- 社員番号の生成
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50)
);

-- 社員番号を'EMP-'プレフィックス付き6桁で表示
SELECT 
    id,
    'EMP-' || LPAD(id::TEXT, 6, '0') AS employee_code,
    name,
    department
FROM employees;

-- 結果例：
-- id | employee_code | name    | department
-- 1  | EMP-000001   | 田中    | 営業部
-- 23 | EMP-000023   | 山田    | 開発部

-- 請求書番号の生成（年月＋連番）
SELECT 
    invoice_id,
    TO_CHAR(invoice_date, 'YYYYMM') || '-' || LPAD(invoice_id::TEXT, 5, '0') AS invoice_number,
    customer_name,
    amount
FROM invoices;

-- 結果例：
-- invoice_id | invoice_number | customer_name | amount
-- 1         | 202401-00001   | A社           | 10000
-- 123       | 202401-00123   | B社           | 25000

TO_CHAR関数 – より高度な数値フォーマット

TO_CHAR関数での0埋め

-- 基本的な0埋めフォーマット
SELECT TO_CHAR(42, '00000');     -- 結果: ' 00042'（先頭にスペースあり）
SELECT TO_CHAR(42, 'FM00000');   -- 結果: '00042'（FMで余分なスペース削除）
SELECT TO_CHAR(42, 'FM000000');  -- 結果: '000042'

-- 様々なフォーマットパターン
SELECT 
    num,
    TO_CHAR(num, 'FM0000') AS four_digits,
    TO_CHAR(num, 'FM000000') AS six_digits,
    TO_CHAR(num, 'FM00000000') AS eight_digits
FROM (VALUES (1), (42), (999), (12345)) AS t(num);

-- 結果：
-- num   | four_digits | six_digits | eight_digits
-- 1     | 0001       | 000001     | 00000001
-- 42    | 0042       | 000042     | 00000042
-- 999   | 0999       | 000999     | 00000999
-- 12345 | 12345      | 012345     | 00012345

フォーマット記号の詳細

-- よく使うフォーマット記号
SELECT 
    -- 0: 数字（0埋め）
    TO_CHAR(42, '0000') AS zero_pad,           -- ' 0042'
    
    -- 9: 数字（スペース埋め）
    TO_CHAR(42, '9999') AS space_pad,          -- '   42'
    
    -- FM: 余分なスペースを削除
    TO_CHAR(42, 'FM0000') AS no_space,         -- '0042'
    
    -- 組み合わせ
    TO_CHAR(42, 'FM999000') AS mixed,          -- '42000'
    
    -- 小数点
    TO_CHAR(42.5, 'FM0000.00') AS decimal,     -- '0042.50'
    
    -- 符号
    TO_CHAR(-42, 'SFM0000') AS with_sign,      -- '-0042'
    TO_CHAR(42, 'SFM0000') AS positive_sign;   -- '+0042'

実践パターン集

パターン1：IDや番号の生成

-- 商品コードの生成（カテゴリー＋連番）
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    category VARCHAR(10),
    name VARCHAR(100)
);

-- カテゴリ別の商品コード生成
WITH product_codes AS (
    SELECT 
        id,
        category,
        name,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY id) AS category_seq
    FROM products
)
SELECT 
    id,
    category || '-' || LPAD(category_seq::TEXT, 4, '0') AS product_code,
    name
FROM product_codes;

-- 結果例：
-- id | product_code | name
-- 1  | FOOD-0001   | りんご
-- 2  | FOOD-0002   | バナナ
-- 3  | BOOK-0001   | PostgreSQL入門
-- 4  | BOOK-0002   | SQL実践ガイド

パターン2：日付時刻との組み合わせ

-- 注文番号（日付＋連番）
CREATE SEQUENCE order_seq;

-- 日付リセット機能付き連番
CREATE OR REPLACE FUNCTION generate_order_number()
RETURNS TEXT AS $$
DECLARE
    today DATE := CURRENT_DATE;
    seq_num INTEGER;
    last_reset DATE;
BEGIN
    -- 最後のリセット日を取得
    SELECT last_value_date INTO last_reset
    FROM sequence_metadata
    WHERE sequence_name = 'daily_order_seq';
    
    -- 日付が変わったらシーケンスをリセット
    IF last_reset IS NULL OR last_reset < today THEN
        ALTER SEQUENCE daily_order_seq RESTART WITH 1;
        UPDATE sequence_metadata 
        SET last_value_date = today
        WHERE sequence_name = 'daily_order_seq';
    END IF;
    
    -- 連番取得
    seq_num := nextval('daily_order_seq');
    
    -- 注文番号生成（例：ORD-20240115-0001）
    RETURN 'ORD-' || TO_CHAR(today, 'YYYYMMDD') || '-' || LPAD(seq_num::TEXT, 4, '0');
END;
$$ LANGUAGE plpgsql;

-- タイムスタンプを含む一意なID
SELECT 
    TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS') || 
    '-' || 
    LPAD((random() * 9999)::INTEGER::TEXT, 4, '0') AS unique_id;
-- 結果例: 20240115143052-0842

パターン3：郵便番号・電話番号の整形

-- 郵便番号の整形
CREATE TABLE addresses (
    id SERIAL PRIMARY KEY,
    postal_code VARCHAR(10),
    prefecture VARCHAR(20),
    city VARCHAR(50)
);

-- 郵便番号を7桁に統一（ハイフン付き）
SELECT 
    postal_code,
    CASE 
        WHEN LENGTH(REGEXP_REPLACE(postal_code, '[^0-9]', '', 'g')) = 7 THEN
            SUBSTRING(LPAD(REGEXP_REPLACE(postal_code, '[^0-9]', '', 'g'), 7, '0'), 1, 3) || 
            '-' || 
            SUBSTRING(LPAD(REGEXP_REPLACE(postal_code, '[^0-9]', '', 'g'), 7, '0'), 4, 4)
        ELSE postal_code
    END AS formatted_postal_code
FROM addresses;

-- 電話番号の整形（市外局番の0埋め）
CREATE OR REPLACE FUNCTION format_phone_number(phone TEXT)
RETURNS TEXT AS $$
DECLARE
    digits TEXT;
BEGIN
    -- 数字のみ抽出
    digits := REGEXP_REPLACE(phone, '[^0-9]', '', 'g');
    
    -- 桁数に応じて整形
    RETURN CASE
        WHEN LENGTH(digits) = 10 THEN
            -- 03-1234-5678 形式
            LPAD(SUBSTRING(digits, 1, 2), 2, '0') || '-' ||
            LPAD(SUBSTRING(digits, 3, 4), 4, '0') || '-' ||
            LPAD(SUBSTRING(digits, 7, 4), 4, '0')
        WHEN LENGTH(digits) = 11 THEN
            -- 090-1234-5678 形式
            LPAD(SUBSTRING(digits, 1, 3), 3, '0') || '-' ||
            LPAD(SUBSTRING(digits, 4, 4), 4, '0') || '-' ||
            LPAD(SUBSTRING(digits, 8, 4), 4, '0')
        ELSE digits
    END;
END;
$$ LANGUAGE plpgsql;

パターン4：会計・金額表示

-- 金額の桁揃え表示
CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    description VARCHAR(200),
    amount DECIMAL(12, 2)
);

-- 金額を12桁で0埋め（整数部10桁＋小数部2桁）
SELECT 
    description,
    amount,
    TO_CHAR(amount, 'FM0000000000.00') AS formatted_amount,
    -- カンマ区切り付き
    TO_CHAR(amount, 'FM999,999,999,990.00') AS comma_separated,
    -- 通貨記号付き
    '¥' || TO_CHAR(amount, 'FM999,999,999,990') AS with_currency
FROM transactions;

-- 結果例：
-- description | amount   | formatted_amount | comma_separated | with_currency
-- 売上       | 1234.56  | 0000001234.56   | 1,234.56       | ¥1,235
-- 仕入       | 98765.00 | 0000098765.00   | 98,765.00      | ¥98,765

バッチ処理での活用

CSVエクスポート用の整形

-- CSV出力用のビュー作成
CREATE OR REPLACE VIEW export_customers AS
SELECT 
    -- 顧客コード（8桁固定）
    LPAD(customer_id::TEXT, 8, '0') AS customer_code,
    
    -- 顧客名（全角20文字固定、不足分はスペース）
    RPAD(customer_name, 40, ' ') AS customer_name_fixed,
    
    -- 郵便番号（ハイフンなし7桁）
    LPAD(REGEXP_REPLACE(postal_code, '[^0-9]', '', 'g'), 7, '0') AS postal_code_7digit,
    
    -- 電話番号（ハイフンなし11桁）
    LPAD(REGEXP_REPLACE(phone, '[^0-9]', '', 'g'), 11, '0') AS phone_11digit,
    
    -- 取引額（12桁、小数点なし、100倍して円単位）
    LPAD((COALESCE(transaction_amount, 0) * 100)::BIGINT::TEXT, 12, '0') AS amount_yen,
    
    -- 登録日（YYYYMMDD形式）
    TO_CHAR(registration_date, 'YYYYMMDD') AS reg_date_8digit
FROM customers;

-- CSV出力
COPY (SELECT * FROM export_customers) 
TO '/tmp/customers.csv' 
WITH (FORMAT CSV, HEADER true);

連番の一括更新

-- 既存データに連番を振り直す
WITH numbered_data AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (ORDER BY created_at, id) AS new_seq
    FROM documents
    WHERE document_type = 'invoice'
)
UPDATE documents d
SET document_number = 'INV-' || TO_CHAR(EXTRACT(YEAR FROM CURRENT_DATE), 'FM0000') || 
                      '-' || LPAD(n.new_seq::TEXT, 6, '0')
FROM numbered_data n
WHERE d.id = n.id;

-- 部門ごとの連番振り直し
WITH dept_numbers AS (
    SELECT 
        employee_id,
        department_id,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY hire_date, employee_id) AS dept_seq
    FROM employees
)
UPDATE employees e
SET employee_code = d.dept_code || '-' || LPAD(dn.dept_seq::TEXT, 4, '0')
FROM dept_numbers dn
JOIN departments d ON d.id = dn.department_id
WHERE e.employee_id = dn.employee_id;

パフォーマンスと注意点

インデックスの考慮

-- 0埋めした値でのインデックス
CREATE INDEX idx_padded_id ON products(LPAD(id::TEXT, 8, '0'));

-- 関数インデックスを使った高速検索
CREATE INDEX idx_formatted_code ON employees((
    'EMP-' || LPAD(id::TEXT, 6, '0')
));

-- 検索時の注意
-- 効率的：インデックスが使える
SELECT * FROM employees 
WHERE 'EMP-' || LPAD(id::TEXT, 6, '0') = 'EMP-000042';

-- 非効率：全件スキャン
SELECT * FROM employees 
WHERE LPAD(id::TEXT, 6, '0') LIKE '%042';

データ型の選択

-- 0埋めした値の保存方法

-- 方法1：生成列として保存（PostgreSQL 12+）
ALTER TABLE products 
ADD COLUMN product_code VARCHAR(10) 
GENERATED ALWAYS AS ('PRD-' || LPAD(id::TEXT, 6, '0')) STORED;

-- 方法2：トリガーで自動生成
CREATE OR REPLACE FUNCTION generate_product_code()
RETURNS TRIGGER AS $$
BEGIN
    NEW.product_code := 'PRD-' || LPAD(NEW.id::TEXT, 6, '0');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_product_code
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION generate_product_code();

-- 方法3：ビューで都度生成（ストレージ節約）
CREATE VIEW products_with_code AS
SELECT 
    *,
    'PRD-' || LPAD(id::TEXT, 6, '0') AS product_code
FROM products;

応用：カスタム関数の作成

汎用的な0埋め関数

-- 柔軟な番号生成関数
CREATE OR REPLACE FUNCTION generate_padded_number(
    prefix TEXT DEFAULT '',
    number BIGINT DEFAULT 0,
    pad_length INTEGER DEFAULT 6,
    suffix TEXT DEFAULT '',
    separator TEXT DEFAULT '-'
) RETURNS TEXT AS $$
BEGIN
    RETURN 
        CASE 
            WHEN prefix = '' THEN ''
            ELSE prefix || separator
        END ||
        LPAD(number::TEXT, pad_length, '0') ||
        CASE 
            WHEN suffix = '' THEN ''
            ELSE separator || suffix
        END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 使用例
SELECT 
    generate_padded_number('ORD', 42, 8),                    -- ORD-00000042
    generate_padded_number('INV', 123, 6, '2024'),          -- INV-000123-2024
    generate_padded_number('', 7, 4),                       -- 0007
    generate_padded_number('USER', 99, 5, '', '_');         -- USER_00099

バーコード・QRコード用フォーマット

-- JANコード（13桁）のチェックデジット計算付き生成
CREATE OR REPLACE FUNCTION generate_jan_code(
    company_code TEXT,  -- 7桁または9桁
    item_code INTEGER   -- 商品コード
) RETURNS TEXT AS $$
DECLARE
    code_without_check TEXT;
    check_digit INTEGER;
    odd_sum INTEGER := 0;
    even_sum INTEGER := 0;
    i INTEGER;
BEGIN
    -- 12桁のコード生成（チェックデジット除く）
    code_without_check := LPAD(company_code, 9, '0') || LPAD(item_code::TEXT, 3, '0');
    
    -- チェックデジット計算
    FOR i IN 1..12 LOOP
        IF i % 2 = 1 THEN
            odd_sum := odd_sum + SUBSTRING(code_without_check, i, 1)::INTEGER;
        ELSE
            even_sum := even_sum + SUBSTRING(code_without_check, i, 1)::INTEGER;
        END IF;
    END LOOP;
    
    check_digit := (10 - ((odd_sum + even_sum * 3) % 10)) % 10;
    
    RETURN code_without_check || check_digit::TEXT;
END;
$$ LANGUAGE plpgsql;

-- QRコード用URL生成
SELECT 
    'https://example.com/product/' || 
    LPAD(product_id::TEXT, 10, '0') || 
    '?batch=' || 
    TO_CHAR(manufacture_date, 'YYYYMMDD') AS qr_url
FROM products;

トラブルシューティング

よくある問題と解決法

-- 問題1：数値が桁数を超える場合
SELECT LPAD('12345'::TEXT, 3, '0');  -- 結果: '12345'（切り捨てられない）

-- 解決：右側から指定桁数を取得
SELECT RIGHT(LPAD('12345'::TEXT, 3, '0'), 3);  -- 結果: '345'

-- 問題2：NULLの扱い
SELECT LPAD(NULL::TEXT, 5, '0');  -- 結果: NULL

-- 解決：COALESCEで対処
SELECT LPAD(COALESCE(column_name, 0)::TEXT, 5, '0');

-- 問題3：負の数の0埋め
SELECT LPAD((-42)::TEXT, 5, '0');  -- 結果: '00-42'（意図しない結果）

-- 解決：符号を分離して処理
SELECT 
    CASE 
        WHEN num < 0 THEN '-' || LPAD(ABS(num)::TEXT, 5, '0')
        ELSE LPAD(num::TEXT, 5, '0')
    END
FROM (VALUES (-42), (42)) AS t(num);