ソフトウェア開発者の日常

こだわりなく書きたいことを書いていきます。

PostgreSQL:to_number()の挙動の不思議なところ

PostgreSQLのデータベースに、
ABCD-E001
ABC-DE001
という形式のデータが格納されています。

データに
ABC/DEF-001
という形式のデータを追加したら、SELECTクエリでエラーが発生しました。

桁数不足?

SELECTクエリでは、to_number関数を使って値の変換をしていて、

SELECT ABS(to_number(data1, '000000')) FROM table1;

としていました。

−があっても処理はできていたので、/がまずいのかと
ABCDEF-001
としても、to_numberはエラーのままです。

もう1文字消して、
ABCDE-001
としたら、to_numberのエラーが解消されました。

数値として扱えない部分が6文字になったらエラーが解消されたので、元の文字列
ABC/DEF-001
のまま処理できるようにするには、0を8文字並べればいいのかもと思いつき、
SELECTクエリを、

SELECT ABS(to_number(data1, '00000000')) FROM table1;

としたら、to_numberのエラーは発生しませんでした。

to_number関数は、数値に変換できない文字列は無視して変換してくれていますが、数値に変換できない文字が数値型書式で指定した文字数を超えてしまうとエラーになるようです。
このあたり、マニュアルにないので、結果からの予測です。
9.8. データ型書式設定関数

正規表現で取り除けばいい?

PostgreSQLにも正規表現での文字列の取り出しができるので、

SELECT regexp_matches(data1, '([0-9]{1,9})') FROM table1;

としてみると、テキスト配列で戻されますが、数値だけ取り出せます。

この値を使うようにするのが、スマートな方法かもしれません。

9.7. パターンマッチ