PostgreSQL 'invalid input syntax for type integer: "abc"' エラーの修正方法

状況

深夜2時。バッチインポートジョブが途中でクラッシュし、テーブルが中途半端な状態になっています。ログには以下が表示されています：

ERROR: invalid input syntax for type integer: "abc"
LINE 1: INSERT INTO orders (user_id, amount) VALUES ('abc', 100);
                                                      ^

またはアプリケーションドライバ経由でこのように表示される場合もあります：

psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type integer: "abc"

PostgreSQLは型に対して厳格です。MySQLとは異なり、"abc"を暗黙的に0に変換することはなく、エラーをスローしてトランザクション全体をロールバックします。深夜2時には厄介ですが、根本原因を修正すれば本当に有用な動作です。

デバッグ：実際に挿入されているものを確認する

エラーメッセージには2つの手がかりがあります：対象の型（integer、numeric、date、uuid）と問題のある正確な値です。そこから始めましょう。

-- 手動で再現する
SELECT 'abc'::integer;
-- ERROR: invalid input syntax for type integer: "abc"

-- 実際のデータのエッジケースをテストする
SELECT '42'::integer;   -- OK: 42を返す
SELECT ' 42 '::integer; -- OK: 空白をトリムする
SELECT '42.5'::integer; -- ERROR: 整数ではない（numericを使うか先に丸める）
SELECT ''::integer;     -- ERROR: 空文字列はゼロではない
SELECT NULL::integer;   -- OK: NULLは常に有効

実際によく見られる原因はこちらです：

• ヘッダー行がデータ行に混入したCSVエクスポート
• NULLが期待される場所での空文字列（""）
• integerカラムに挿入される小数（"12.50"）
• 数値に見えるが文字を含むID（"USR-042"）
• ロケール形式の数値（1234の代わりに"1,234"）

挿入前に問題のある行を見つける

ステージングテーブルやCSVからロードする場合は、本番テーブルに触れる前に問題を検出しましょう。1つの正規表現でほとんどの問題を検出できます：

-- CSVをすべてTEXT型のカラムを持つステージングテーブルにロードした前提
SELECT user_id, amount
FROM staging_orders
WHERE user_id !~ '^-?[0-9]+$'
   OR user_id = '';

-- 空白も処理する、より汎用的なバージョン
SELECT user_id
FROM staging_orders
WHERE user_id IS NOT NULL
  AND user_id !~ '^\s*-?[0-9]+\s*$';

PostgreSQL 14以降ではよりクリーンなアプローチとしてカスタムのtry_cast関数を作成できますが、上記の正規表現はすべてのバージョンで動作します。

修正1：挿入前にデータをクレンジングする

ソースデータを修正することは、SQLで回避策を講じるよりも優れています。あまりスマートではありませんが、より信頼性が高いです。

-- 空文字列はエラーにならずNULLになる
INSERT INTO orders (user_id, amount)
SELECT
  NULLIF(user_id, '')::integer,
  amount::numeric
FROM staging_orders
WHERE user_id ~ '^-?[0-9]+$' OR user_id IS NULL;

-- "1,234"のようなロケール形式の数値からカンマを除去する
INSERT INTO orders (user_id, amount)
SELECT
  user_id::integer,
  REPLACE(amount, ',', '')::numeric
FROM staging_orders;

修正2：クエリ実行前にアプリケーションコードでバリデーションする

ユーザー入力や外部APIからの不正なデータ？アプリケーション層でキャッチしましょう — Postgresに届く前に。

Python（psycopg2 / psycopg3）：

def safe_insert_order(conn, user_id_raw, amount_raw):
    try:
        user_id = int(user_id_raw)
    except (ValueError, TypeError):
        raise ValueError(f"Invalid user_id: {user_id_raw!r}")

    with conn.cursor() as cur:
        cur.execute(
            "INSERT INTO orders (user_id, amount) VALUES (%s, %s)",
            (user_id, amount_raw)
        )
    conn.commit()

Node.js（pg）：

async function insertOrder(client, userIdRaw, amount) {
  const userId = parseInt(userIdRaw, 10);
  if (isNaN(userId)) {
    throw new Error(`Invalid user_id: ${userIdRaw}`);
  }
  await client.query(
    'INSERT INTO orders (user_id, amount) VALUES ($1, $2)',
    [userId, amount]
  );
}

修正3：CHECK制約またはドメイン型を追加する

不正なデータがデータベースレベルで入り込み続ける場合は、制約でロックしましょう。これにより、バグのあるマイグレーションスクリプトでも問題が静かに発生することはありません。

-- 1つのカラムに対するシンプルなチェック制約
ALTER TABLE orders
  ADD CONSTRAINT chk_user_id_positive CHECK (user_id > 0);

-- または複数のテーブルで再利用可能なドメイン型を定義する
CREATE DOMAIN positive_int AS integer CHECK (VALUE > 0);

ALTER TABLE orders ALTER COLUMN user_id TYPE positive_int;

修正4：COPYとステージングテーブルによる一括ロード

大規模なCSVインポートの場合は、INSERTを完全にスキップしましょう。まずすべてテキストのステージングテーブルにロードし、バリデーションしてからキャストします。ステージングロードの失敗からの復旧は簡単です。本番テーブルのトランザクション途中からの復旧はそうではありません。

-- ステップ1：すべてTEXT型カラムのステージングテーブル
CREATE TEMP TABLE staging_orders (
  user_id    TEXT,
  amount     TEXT,
  created_at TEXT
);

-- ステップ2：生のCSVをロード
COPY staging_orders FROM '/tmp/orders.csv' CSV HEADER;

-- ステップ3：バリデーション — これは0を返すべき
SELECT COUNT(*) FROM staging_orders
WHERE user_id !~ '^[0-9]+$';

-- ステップ4：バリデーション通過後のみキャストして挿入
INSERT INTO orders (user_id, amount, created_at)
SELECT
  user_id::integer,
  amount::numeric,
  created_at::timestamptz
FROM staging_orders;

修正が機能したことを確認する

-- 行数はソースファイルと一致するべき
SELECT COUNT(*) FROM orders;

-- 予期しないNULLが混入していないことを確認
SELECT COUNT(*) FROM orders WHERE user_id IS NULL;

-- 最近挿入された行をスポットチェック
SELECT user_id, amount FROM orders ORDER BY id DESC LIMIT 10;

インポートを明示的なトランザクションでラップした場合は、コミットされたことを確認しましょう：

-- psqlの場合
\echo :AUTOCOMMIT
-- または
SELECT txid_current();

得られた教訓

• **外部データは決して想定した型ではありません。**型付きカラムに挿入する前に、すべてをバリデーションまたはサニタイズしましょう — CSV、Webhook、レガシーシステムはすべて嘘をつきます。
• **ステージングテーブルは手間をかける価値があります。**TEXTとしてロードし、バリデーションしてからキャストしましょう。500,000行のインポートの450,000行目に3つの不正な行がある場合、初めてその価値に感謝するでしょう。
• PostgreSQLでは空文字列≠NULLです。NULLIF(val, '')は頼れる存在です。MySQLは数値カラムで""を静かに受け入れますが、PostgreSQLはそうしません。
• **パラメータ化クエリは型変換を自動で行います。**適切に型付けされたPythonやNodeの値と共に%sや$1プレースホルダーを使いましょう。文字列連結はその安全網を完全にバイパスします。
• **エラーメッセージにはすでに答えが含まれています。**PostgreSQLは常に問題のある値を引用符で囲んで表示します — データセット全体を探し回るのではなく、正確なソース行まで遡るために活用しましょう。