PostgreSQL『cast from type to integer is not allowed』の原因と対処

ERROR:  cannot cast type boolean to integer
ERROR:  cannot cast type jsonb to integer
ERROR:  cannot cast type uuid to integer
ERROR:  cannot cast type bytea to integer
-- 似た仲間
ERROR:  invalid input syntax for type integer: "12,345円"

-- 問題の式や列の実型を見る
SELECT pg_typeof(col) AS actual_type, col FROM t LIMIT 5;

-- 文字列列の“中身”の傾向
SELECT
  COUNT(*) FILTER (WHERE col ~ '^-?\d+$') AS pure_int_like,
  COUNT(*) FILTER (WHERE col ~ '[^\d-]')  AS has_non_digit,
  COUNT(*) FILTER (WHERE btrim(col) = '') AS empty_str
FROM t;

-- 千区切り・通貨記号・空白などを除去 → 数字だけになったらキャスト
SELECT CASE
         WHEN col IS NULL THEN NULL
         ELSE
           NULLIF(regexp_replace(col, '[^\d-]', '', 'g'), '')::int
       END AS col_int
FROM t;

-- 形式が一定なら to_number でも（数値→丸めて::int）
SELECT to_number(col, 'FM999G999G999')::int FROM t;

-- “確実に数字のときだけ”キャスト（try_cast相当）
SELECT CASE WHEN col ~ '^\s*-?\d+\s*$' THEN col::int END AS col_int FROM t;

-- NG
SELECT true::int;  -- ERROR: cannot cast type boolean to integer

-- OK（CASEまたは bool::int を自作しない）
SELECT CASE WHEN flag THEN 1 ELSE 0 END AS flag_int FROM t;

-- 頻出なら生成列やビューで統一
ALTER TABLE t ADD COLUMN flag_i int GENERATED ALWAYS AS
  (CASE WHEN flag THEN 1 ELSE 0 END) STORED;

-- NG
SELECT payload::int FROM t;            -- cannot cast type jsonb to integer

-- OK（→> または ->> で“文字列”を取り出してから）
SELECT (payload->>'age')::int FROM t;

-- 配列の要素を整数化
SELECT (elem->>0)::int AS first_num
FROM t, LATERAL jsonb_array_elements_text(payload->'nums') AS elem(elem_text);

-- “数値型として格納している”なら cast 前に ::text を経由しない
SELECT (payload->'age')::int          -- これは不可。必ず ->> で文字列化してから。

-- Unix秒（整数）にしたい
SELECT EXTRACT(EPOCH FROM ts)::bigint AS ts_sec FROM t;

-- 日付を yyyymmdd の整数にしたい
SELECT to_char(d, 'YYYYMMDD')::int AS yyyymmdd FROM t;

-- 月数/年数など（整数）
SELECT EXTRACT(YEAR FROM d)::int AS yyyy, EXTRACT(MONTH FROM d)::int AS mm FROM t;

-- uuid → int は不可。必要なら“ハッシュ等の代理キー”に変換してから
SELECT hashtextextended(id::text, 0) AS id_hash_int8 FROM u;  -- int8（bigint）推奨

-- bytea はバイナリ。整数を取り出すなら get_byte/substring などで“数値化”
SELECT get_byte(data, 0)::int AS first_byte FROM files;

-- enum は整数に直接キャスト不可。“序数”が必要なら enum_range + array_position
SELECT array_position(enum_range(NULL::myenum), val) - 1 AS ordinal FROM e;

-- array は“要素”を取り出してから
SELECT (arr[1])::int FROM t;  -- または UNNEST(arr)::int

-- ドメイン型は“基底型へ戻してから”
CREATE DOMAIN posint AS int CHECK (VALUE > 0);
-- posint → int は OK（基底が int）。別基底なら (val::basetype)::int の二段。

-- 変換失敗候補を先に隔離（監査用）
CREATE MATERIALIZED VIEW bad_rows AS
SELECT id, raw_value
FROM src
WHERE raw_value IS NOT NULL
  AND NOT (raw_value ~ '^\s*-?\d+\s*$');

-- 正常系は安全キャストで流す
CREATE VIEW cleaned AS
SELECT id, CASE WHEN raw_value ~ '^\s*-?\d+\s*$' THEN raw_value::int END AS value_int
FROM src;

-- 生成列で正規化
ALTER TABLE orders
  ADD COLUMN amount_i int GENERATED ALWAYS AS
    (NULLIF(regexp_replace(amount_txt, '[^\d-]', '', 'g'), '')::int) STORED;

-- 新規データが整数化可能かを保証するチェック
ALTER TABLE orders
  ADD CONSTRAINT amount_txt_int_check
  CHECK (amount_txt IS NULL OR amount_txt ~ '^\s*-?\d+\s*$');

DROP TABLE IF EXISTS demo;
CREATE TABLE demo(
  id    serial PRIMARY KEY,
  b     boolean,
  j     jsonb,
  ts    timestamp,
  u     uuid,
  s     text
);

INSERT INTO demo(b,j,ts,u,s) VALUES
(true,  '{"age":"19"}', now(), gen_random_uuid(), '12,345円'),
(false, '{"age":25}',   now(), gen_random_uuid(), '  42  '),
(true,  '{"age":"x"}',  now(), gen_random_uuid(), '');

-- NG：直接キャスト
-- SELECT b::int, j::int, u::int, ts::int, s::int FROM demo;  -- すべて失敗可能

-- OK：型別の正しい変換
SELECT
  CASE WHEN b THEN 1 ELSE 0 END                 AS b_i,
  NULLIF(j->>'age','')::int                     AS age_i,         -- 文字列数値/NULLならOK
  EXTRACT(EPOCH FROM ts)::bigint                AS ts_sec,
  hashtextextended(u::text, 0)                  AS u_hash_i8,
  NULLIF(regexp_replace(s, '[^\d-]', '', 'g'),'')::int AS s_i
FROM demo;

□ pg_typeof() で“元の型”を把握したか
□ 文字列→整数は正規表現ガードや前処理を入れているか
□ boolean/jsonb/uuid/date/enum/bytea/array/ドメイン型は“専用の変換手段”を使っているか
□ 変換できない値（空文字・単位付き・区切り付き）を事前に除去/隔離しているか
□ ビュー/生成列/制約で“常に整数化可能”を設計で担保できるか