PostgreSQL「ERROR: invalid byte sequence for encoding」の原因と対処

ERROR:  invalid byte sequence for encoding "UTF8": 0x00
CONTEXT: COPY my_table, line 42

-- サーバ/セッション側の状況
SHOW server_encoding;   -- ほとんどの環境は UTF8
SHOW client_encoding;   -- ドライバ/psql が設定
SELECT datname, pg_encoding_to_char(encoding) AS db_enc
FROM pg_database ORDER BY 1;

-- psql ならセッションで明示
\encoding UTF8          -- または SET client_encoding TO 'UTF8';

-- 1) ファイルの実エンコーディングを特定（file コマンドや nkf -g 等）
file data.csv
nkf -g data.csv

-- 2) 取り込み前に正規化（iconv/nkf で UTF-8 へ）
iconv -f SHIFT_JIS -t UTF-8 data.csv > data.utf8.csv
# NUL や不正バイトを捨てたい場合は //IGNORE を併用（要要件確認）
iconv -f SHIFT_JIS -t UTF-8//IGNORE data.csv > data.utf8.csv

-- 3) 取り込み（BOM/改行も確認）
\encoding UTF8
\copy my_schema.my_table(col1, col2, ...) FROM 'data.utf8.csv' CSV HEADER

-- サーバサイドでの変換取り込み（PostgreSQL サーバ OS に iconv がある前提）
COPY my_schema.my_table(col1, col2, …)
FROM PROGRAM 'iconv -f SHIFT_JIS -t UTF-8 /path/data.csv'
WITH (FORMAT csv, HEADER true);

-- 接続直後に 1 回だけ実行（コネクションプールならプール初期化/検証時に）
SET client_encoding TO 'UTF8';

# 事前に NUL を除去（不可視文字なのでファイルで処理するのが安全）
tr -d '\000' < data.csv > data.nonul.csv

# iconv で変換と同時に NUL を落とす例（入力が UTF-8 前提）
iconv -f UTF-8 -t UTF-8 -c data.csv > data.clean.csv

# 1) SQL_ASCII DB をダンプ（ロケール情報も記録）
pg_dump -d legacy_db > legacy.sql

# 2) ダンプを iconv で UTF-8 に
iconv -f CP932 -t UTF-8 legacy.sql > legacy.utf8.sql

# 3) 新しい UTF8 DB へリストア
createdb -E UTF8 newdb
psql -d newdb -f legacy.utf8.sql

-- COPY で "line 12345" と出たら、その行周辺を抜き出して目視確認
sed -n '12340,12350p' data.csv | nl -ba

-- 行番号が得られない/巨大ファイルなら半分ずつに分割して再試行
split -l 1000000 data.csv part_
\copy my_table FROM 'part_aa' CSV;  -- 片方が通る/落ちるで範囲が半分になる

-- 例: 列 col に「本来は UTF-8 だったのに CP1252 として解釈され文字化け」
-- 手順: まず現状の文字列を "CP1252 としてバイト化" → そのバイト群を "UTF-8 として再解釈"
UPDATE my_schema.t
SET col = convert_from(convert_to(col, 'WIN1252'), 'UTF8')
WHERE id IN (...条件...);

-- 逆方向（UTF-8 として解釈されたが本来は CP1252 だった）
UPDATE my_schema.t
SET col = convert_from(convert_to(col, 'UTF8'), 'WIN1252')
WHERE id IN (...条件...);

CREATE OR REPLACE FUNCTION decode_text(b bytea, enc name DEFAULT 'UTF8')
RETURNS text LANGUAGE sql IMMUTABLE AS $$
  SELECT convert_from(b, enc)
$$;

-- 使用例：HEX 文字列を bytea 化してから UTF-8 として解釈
SELECT decode_text(decode('E38182', 'hex'), 'UTF8');  -- "あ"

CREATE OR REPLACE FUNCTION try_decode_text(b bytea, enc name DEFAULT 'UTF8')
RETURNS text LANGUAGE plpgsql IMMUTABLE AS $$
BEGIN
  RETURN convert_from(b, enc);
EXCEPTION WHEN character_not_in_repertoire OR invalid_byte_sequence_for_encoding THEN
  -- 必要ならログし、U+FFFD を返す
  RETURN E'\uFFFD';
END$$;

#!/usr/bin/env bash
set -euo pipefail
in="$1"; out="${2:-/tmp/normalized.csv}"
# 想定: 入力は CP932、NUL/不正バイトは除去、LF 統一、BOM 除去
nkf -w -W --oc=UTF-8 --in-place "$in" 2>/dev/null || true
iconv -f CP932 -t UTF-8//IGNORE "$in" | tr -d '\000' | sed '1s/^\xEF\xBB\xBF//' > "$out"
echo "normalized -> $out"