PostgreSQLの型変換 ― 文字列と数値のCASTと::、invalid input syntaxの直し方

※この記事にはアフィリエイト広告（プロモーション）が含まれます。

PostgreSQL の型変換は CAST(値 AS 型) か 値::型 で書きます。この記事の出力は PostgreSQL 18.6（Ubuntu 26.04）で採りました。

まず見る（結論）

SELECT '123'::int;                       -- 123
SELECT CAST('123' AS integer);           -- 123
SELECT '12.5'::numeric::int;             -- 13（numeric を経由すると四捨五入）
SELECT replace('1,234', ',', '')::int;   -- 1234（カンマを外してから）
SELECT 123::text;                        -- '123'
SELECT to_char(5, 'FM00000');            -- '00005'
SELECT pg_input_is_valid('12.5', 'integer');  -- f（変換できるかの判定・16 以降）

MySQL と違い、PostgreSQL は変換できない文字列を 0 にしたり途中で切ったりしません。必ずエラーになります。

文字列 → 数値で出るエラー

エラーの SQLSTATE は 22P02 です。直し方は値の形で変わります。

• 小数 … '12.5'::numeric で受ける。整数が要るなら '12.5'::numeric::int（13）か trunc('12.5'::numeric)::int（12）
• カンマ入り … replace(値, ',', '')::int か to_number('1,234', '9,999')
• 全角数字 … translate(値, '０１２３４５６７８９', '0123456789')::int
• 空文字 … nullif(値, '')::int で NULL にしてから変換
• 21億を超える … ::bigint、桁が多いなら ::numeric

丸め方は経由する型で変わる

numeric からの変換は 0 から遠い側へ丸め、double precision（float8）からの変換は偶数側へ丸めます。.5 の値を扱うなら numeric を経由します。

to_number は書式より長い桁を黙って捨てる

to_number('12,345,678', '9,999') は 123、to_number('1234', '99') は 12 を返しました。エラーも警告も出ません。書式の桁数は入りうる最大の桁で書くか、カンマを replace で外して ::numeric にします。

数値 → 文字列・0埋め

SELECT 12.50::text;                             -- '12.50'（numeric の桁はそのまま）
SELECT to_char(5, 'FM00000');                   -- '00005'
SELECT to_char(5, '00000');                     -- ' 00005'（先頭に符号用の空白）
SELECT lpad(5::text, 5, '0');                   -- '00005'
SELECT to_char(1234567.891, 'FM9,999,999.00');  -- '1,234,567.89'

書式の一覧は to_char の記事に書きました。1 || 2 は operator does not exist: integer || integer になります。数値をつなぐときは片方を ::text にします（文字列結合の記事）。

text 列を数値と比べる・並べる

code（text）に '10' '9' '100' '2' '08' が入った表で試しました。

code::int は列に1行でも数字以外が入っていると、その行で invalid input syntax になって文全体が失敗します。sum(amount_txt::int) も '1,200' の行で止まりました。

変換できる行だけ使う

SELECT pg_input_is_valid('123', 'integer');          -- t
SELECT pg_input_is_valid('2147483648', 'integer');   -- f（範囲外も f）
SELECT * FROM pg_input_error_info('12.5', 'integer');
-- message: invalid input syntax for type integer: "12.5" / sql_error_code: 22P02

SELECT id, CASE WHEN pg_input_is_valid(amount_txt, 'integer') THEN amount_txt::int END
  FROM codes;                                        -- 変換できない行は NULL

pg_input_is_valid は PostgreSQL 16 で入った関数です。15 以前は amount_txt ~ '^[0-9]+$' のような正規表現で判定します。正規表現では範囲外の値を弾けません。

列の型を数値に変える

ALTER TABLE codes ALTER COLUMN code TYPE integer は column "code" cannot be cast automatically to type integer で失敗し、HINT に USING code::integer が出ます。USING code::integer を付けると通り、'08' は 8 になりました。変換できない値が1行でもあると失敗するので、先に pg_input_is_valid で該当行を探します。表のロックについては ALTER TABLE の記事に書きました。

整数どうしの割り算

率や平均を出すときは片方を numeric にします。

ほかの型への変換

• '2026-11-08'::date と '20261108'::date はどちらも 2026-11-08。書式を指定するなら to_date('2026/11/08', 'YYYY/MM/DD')
• 'true' '1' 'yes' 'on' は ::boolean で true。'2'::boolean は invalid input syntax for type boolean: "2"
• 1 + '2.5' は invalid input syntax for type integer: "2.5"。型の付いていない文字列は相手の型（ここでは integer）として読まれる。1 + '2.5'::numeric と型を付ける

関連: PostgreSQLのtext型 ／ PostgreSQLにNVLは無い ― COALESCE ／ PostgreSQLの日付フォーマット