to_number関数で「”numeric”型の入力構文が不正です」のエラーが出たら

PostgreSQL
2023/01/09

このカテゴリーでは競馬予想プログラミング以外でも役に立つ、PostgreSQLの汎用的な技術について解説します。

数値型を想定していた項目にスペース(空白)など数値以外の、想定外の値が設定されていた場合、to_number関数は「”numeric”型の入力構文が不正です」というエラーを返します。

これを回避するには、正規表現とCASE式を組み合わせて数値の場合だけto_number関数を使うようにすればOKです。

※ここでは「列名1」が2桁の文字列とします。’99’の書式のとこ。

CASE WHEN 列名1 ~ '[0-9]' THEN to_number(列名1, '99') END

上記のSQLのステップを日本語に変換すると、

  1. CASE WHEN → 正規表現で「列名1」が数値だけの文字列か？チェック
  2. THEN → 数値だけなら「列名1」にto_numberを使う
  3. 数値じゃない場合は無視して null にする

となります。さらに応用編として、数値じゃない場合 null じゃなくて0にしたい場合は、次のように「ELSE」を加えて0を指定すればOK。

CASE WHEN 列名1 ~ '[0-9]' THEN to_number(列名1, '99') ELSE 0 END