PostgreSQL「ERROR: invalid input syntax for type」の原因と対処

-- 整数：カンマ/全角/空白混じり
SELECT '1,234'::int;        -- NG: invalid input syntax for type integer
SELECT replace('1,234', ',', '')::int;  -- OK

-- 数値：単位や通貨記号
SELECT '¥12,345'::numeric;  -- NG
SELECT to_number('¥12,345', 'L9G999');  -- OK（ロケール/フォーマットで吸収）

-- 真偽値：想定外の表記
SELECT 'TRUE'::boolean;     -- OK（大小無視）
SELECT 'yes'::boolean;      -- OK
SELECT 'Y'::boolean;        -- NG（Y単体は不可）
SELECT CASE WHEN lower('Y') IN ('t','true','1','yes','on') THEN true ELSE false END; -- OK

-- 日付/時刻：書式不一致
SELECT '2024/12/31'::date;  -- NG
SELECT to_date('2024/12/31','YYYY/MM/DD'); -- OK

-- timestamp：ISO以外
SELECT '31-12-2024 23:59'::timestamp;    -- NG
SELECT to_timestamp('31-12-2024 23:59','DD-MM-YYYY HH24:MI'); -- OK

-- UUID：桁/ハイフン位置不正
SELECT '550e8400e29b41d4a716446655440000'::uuid;  -- NG
SELECT '550e8400-e29b-41d4-a716-446655440000'::uuid;  -- OK

-- JSON：単一引用符や末尾カンマ
SELECT '{"k": "v",}'::json;  -- NG
SELECT '{"k": "v"}'::json;   -- OK

-- 配列：リテラル書式
SELECT '{a,b,c}'::text[];          -- OK（要引用に注意）
SELECT '{A,B,C}'::int[];           -- NG
SELECT string_to_array('1,2,3', ',')::int[];  -- OK

-- 1) 汎用：記号類の除去→numeric
SELECT regexp_replace(' ¥12,345.67円 ', '[^0-9\.\-]', '', 'g')::numeric;  -- 12345.67

-- 2) to_number：フォーマットで吸収
SELECT to_number('¥12,345', 'L9G999');      -- L:通貨, G:桁区切り
SELECT to_number('1.234,56', '9G999D99');   -- 欧州型(, が小数点)

-- 3) 全角→半角→キャスト
SELECT replace(translate('１２３，４５６', '０１２３４５６７８９，．', '0123456789,.'), ',', '')::bigint;

-- 入力が複数フォーマットの可能性：CASEで順次試行
WITH raw(s) AS (VALUES ('2025/09/10 23:59'), ('10-09-2025 23:59'))
SELECT s,
  COALESCE(
    to_timestamp(s, 'YYYY/MM/DD HH24:MI'),
    to_timestamp(s, 'DD-MM-YYYY HH24:MI')
  ) AS ts
FROM raw;

-- 変換前の簡易検証（YYYY-MM-DDのみ通す例）
SELECT CASE WHEN s ~ '^\d{4}-\d{2}-\d{2}$' THEN s::date ELSE NULL END
FROM (VALUES ('2025-09-10'),('2025/09/10')) t(s);

-- 受け入れ関数：よくある表記を true/false/NULL に正規化
CREATE OR REPLACE FUNCTION parse_bool(text)
RETURNS boolean LANGUAGE sql IMMUTABLE AS $$
  SELECT CASE
    WHEN $1 IS NULL THEN NULL
    WHEN lower(btrim($1)) IN ('t','true','1','yes','y','on')  THEN true
    WHEN lower(btrim($1)) IN ('f','false','0','no','n','off') THEN false
    ELSE NULL
  END
$$;

-- 使用例
SELECT parse_bool('Y'), parse_bool('OFF'), parse_bool('??');  -- t, f, NULL

-- 正規表現で先に検査
SELECT CASE WHEN s ~* '^[0-9a-f]{8}-([0-9a-f]{4}-){3}[0-9a-f]{12}$' THEN s::uuid END AS id
FROM (VALUES ('550e8400-e29b-41d4-a716-446655440000'), ('oops')) v(s);

-- 生成は関数で（pgcrypto拡張）
CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT gen_random_uuid();

-- 入力前に JSON として妥当か検査（妥当なら jsonb に）
SELECT CASE WHEN jsonb_typeof(jsonb_strip_nulls(s::jsonb)) IS NOT NULL
            THEN s::jsonb END AS jb
FROM (VALUES ('{"k":"v"}'), ('{"k": "v",}')) t(s);  -- 後者はエラー → 事前に検査/修正する

-- キーや小数点を正規化する整形例（アプリ側が望ましいが SQL でも可能）
SELECT jsonb_build_object('price', to_number(jb->>'price','9G999D99'))
FROM (SELECT '{"price":"1,234.56"}'::jsonb AS jb) t;

-- CSV文字列から int[] へ
SELECT string_to_array('1,2,3', ',')::int[];

-- 任意の区切りやトリムも考慮
SELECT array_remove(  -- 空文字を取り除く
         ARRAY(
           SELECT NULLIF(btrim(x),'')
           FROM unnest(string_to_array('a| b | |c', '|')) AS x
         ),
         NULL
       )::text[];

-- COPY のエラーには CONTEXT で行番号が含まれることが多い
COPY mytbl(col_int, col_date) FROM '/tmp/in.csv' CSV HEADER;

-- 行番号から抜き出して確認
sed -n '12340,12350p' /tmp/in.csv | nl -ba

-- まずは all text のステージ表へ
CREATE TABLE staging (col_int text, col_date text, col_json text);
\copy staging FROM '/tmp/in.csv' CSV HEADER

-- 検証・正規化して本表へ
INSERT INTO mytbl(col_int, col_date, col_jsonb)
SELECT
  NULLIF(regexp_replace(col_int,'[^0-9\-]','','g'),'')::int,
  to_date(col_date,'YYYY/MM/DD'),
  col_json::jsonb
FROM staging;

-- 直接変換は危険（途中で 22P02 になる）
-- ALTER TABLE t ALTER COLUMN c TYPE int USING c::int; -- NGの可能性

-- ステップ式：一時列に安全変換→検証→入替
ALTER TABLE t ADD COLUMN c_int int;
UPDATE t SET c_int = NULLIF(regexp_replace(c, '[^0-9\-]', '', 'g'),'')::int;
-- 検証（失敗件数を洗い出す）
SELECT COUNT(*) FILTER (WHERE c IS NOT NULL AND c_int IS NULL) AS failed FROM t;
-- 問題なければ入替
ALTER TABLE t DROP COLUMN c;
ALTER TABLE t RENAME COLUMN c_int TO c;

-- 1) 正規表現で「合格の形」だけを許す（電話番号例）
ALTER TABLE users
ADD CONSTRAINT phone_like CHECK (phone ~ '^\+?\d{7,15}$');

-- 2) 使い回せるドメイン
CREATE DOMAIN email_text AS text
  CHECK (VALUE ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$');
CREATE TABLE contacts (id bigserial, email email_text);

-- 例外を NULL に変換する汎用ラッパ（必要に応じてログも可能）
CREATE OR REPLACE FUNCTION try_int(text) RETURNS int
LANGUAGE plpgsql IMMUTABLE AS $$
BEGIN
  RETURN $1::int;
EXCEPTION WHEN invalid_text_representation THEN
  RETURN NULL;
END$$;

CREATE OR REPLACE FUNCTION try_date(text, fmt text DEFAULT 'YYYY-MM-DD')
RETURNS date LANGUAGE plpgsql IMMUTABLE AS $$
BEGIN
  RETURN to_date($1, fmt);
EXCEPTION WHEN others THEN
  RETURN NULL;
END$$;

-- 使用例
SELECT try_int('1,234'), try_date('2025/09/10','YYYY/MM/DD');

-- 例外を NULL に変換する汎用ラッパ（必要に応じてログも可能）
CREATE OR REPLACE FUNCTION try_int(text) RETURNS int
LANGUAGE plpgsql IMMUTABLE AS $$
BEGIN
  RETURN $1::int;
EXCEPTION WHEN invalid_text_representation THEN
  RETURN NULL;
END$$;

CREATE OR REPLACE FUNCTION try_date(text, fmt text DEFAULT 'YYYY-MM-DD')
RETURNS date LANGUAGE plpgsql IMMUTABLE AS $$
BEGIN
  RETURN to_date($1, fmt);
EXCEPTION WHEN others THEN
  RETURN NULL;
END$$;

-- 使用例
SELECT try_int('1,234'), try_date('2025/09/10','YYYY/MM/DD');

-- P1: '1,234'::int → replace(… , ',', '')::int
-- P2: '¥12,345'::numeric → to_number('¥12,345','L9G999')
-- P3: '2025/09/10'::date → to_date(…,'YYYY/MM/DD')
-- P4: '31-12-2024 23:59'::timestamp → to_timestamp(…,'DD-MM-YYYY HH24:MI')
-- P5: 'y'::boolean → parse_bool('y')
-- P6: 'oops'::uuid → CASE WHEN s ~* uuid_regex THEN s::uuid END
-- P7: '{"k": v}'::json → 正しい JSON（キー/文字列をダブルクオート）
-- P8: '1,2,3'::int[] → string_to_array('1,2,3', ',')::int[]
-- P9: ALTER TABLE … TYPE … USING 直接キャスト → 一時列→検証→入替