PostgreSQL: TO_NUMBER関数で文字列を数値に変換する方法

PostgreSQL: TO_NUMBER関数による文字列から数値への高精度変換ガイド

データは様々な形式でデータベースに格納されますが、多くの場合、文字列として保存された数値を、計算や分析のために本来の数値型に変換する必要があります。PostgreSQLでは、このようなデータ型変換を効率的かつ柔軟に行うためのいくつかの方法が提供されています。その中でも、特に強力で多機能なのが TO_NUMBER 関数です。

この記事では、PostgreSQLの TO_NUMBER 関数に焦点を当て、その基本的な使い方から、複雑な書式指定、エラー処理、さらには実際の応用例に至るまで、詳細かつ網羅的に解説します。約5000語という分量で、この関数のあらゆる側面を深掘りし、読者の皆様が自信を持って TO_NUMBER を使いこなせるようになることを目指します。

1. はじめに：なぜ文字列を数値に変換する必要があるのか？

データベースに格納されるデータは、テキストファイルからのインポート、ユーザーからの入力、他のシステムからの連携など、様々なソースからやってきます。これらのデータは、しばしば数値であるべきものが文字列型（VARCHAR, TEXTなど）として格納されてしまうことがあります。

文字列として格納された数値をそのままにしておくと、以下のような問題が発生します。

数値計算ができない: +, -, *, / といった算術演算子を直接適用できません。文字列の連結が行われてしまいます（例: '10' + '20' は '1020' になる可能性があります）。
数値としての比較ができない: '100' と '20' を比較すると、文字列比較規則に従い '100' が小さく（先に）なります。数値として '100' が '20' より大きいという正しい比較ができません。
集計関数が使えない: SUM, AVG, MIN, MAX といった数値型の列に適用される集計関数が使えません。
データの整合性が保たれにくい: 数値以外の不正な文字列が混入しても、文字列型であればエラーにならずに格納されてしまう可能性があります。
パフォーマンスの低下: 文字列操作は、数値操作に比べて一般的にオーバーヘッドが大きくなります。また、数値としてインデックスを作成することができません。

これらの問題を解決し、データの潜在能力を最大限に引き出すためには、文字列として格納された数値を適切な数値型（INTEGER, BIGINT, DECIMAL/NUMERIC, REAL, DOUBLE PRECISIONなど）に変換する必要があります。

2. PostgreSQLにおける数値変換の選択肢

PostgreSQLで文字列を数値に変換する方法はいくつか存在します。

キャスト演算子 (::) または CAST 関数: これは最もシンプルで一般的な方法です。
sql SELECT '123'::INTEGER; SELECT CAST('456.78' AS NUMERIC);
この方法は、入力文字列が標準的な数値形式（小数点や符号のみを含む）である場合に非常に便利です。しかし、通貨記号、カンマ（桁区切り）、括弧によるマイナス表現など、特定の書式を持つ文字列には対応できません。
TO_NUMBER 関数: これこそがこの記事の主役です。入力文字列が特定の非標準的な書式を持っている場合に、その書式を指定することで正確に数値へ変換できる強力なツールです。

それぞれの方法には利点と欠点があり、変換したい文字列の形式に応じて適切な方法を選択する必要があります。TO_NUMBER 関数は、特に多様な書式を持つ外部データを取り込む際にその真価を発揮します。

3. TO_NUMBER関数の基本

TO_NUMBER 関数は、指定された書式パターンに従って入力文字列を解析し、数値型に変換します。その基本的な構文は以下の通りです。

sql TO_NUMBER(string, format)

string: 変換したい文字列式です。
format: 入力文字列 (string) を解析するための書式モデルを示す文字列です。この書式モデルが TO_NUMBER 関数の柔軟性の鍵となります。

TO_NUMBER 関数は、デフォルトでは NUMERIC 型の値を返します。NUMERIC 型は、任意精度をサポートするため、小数点以下の桁数が多い数値や、非常に大きな/小さな数値を正確に扱うのに適しています。これにより、元の文字列が整数形式であっても小数点形式であっても、精度を損なうことなく変換することが可能です。

基本的な例を見てみましょう。

“`sql
SELECT TO_NUMBER(‘123’, ‘999’);
— 結果: 123 (NUMERIC型)

SELECT TO_NUMBER(‘123.45’, ‘999.99’);
— 結果: 123.45 (NUMERIC型)
“`

この例では、'999' や '999.99' という書式パターンを使用しています。これらの書式パターンは、入力文字列の構造を関数に伝える役割を果たします。次のセクションで、この format 文字列で指定できる様々な書式要素（書式モデル）について詳しく見ていきます。

4. TO_NUMBER関数の書式モデル（format）の詳細

TO_NUMBER 関数の強力さは、その format 引数にあります。この引数に指定する書式モデルは、入力文字列がどのように構成されているかを示し、関数がそれを正しく解釈するためのルールを提供します。書式モデルは、様々な要素を組み合わせて作成されます。以下に、TO_NUMBER で使用できる主な書式要素を詳述します。

これらの書式要素は、入力文字列中の対応する文字と一致する必要があります。一致しない場合や、書式モデルで指定されていない文字が入力文字列中に現れた場合、通常はエラーが発生します。

4.1. 数字のプレースホルダー

9: 数値を表します。入力文字列中の数字に対応します。この要素に対応する入力文字列中の桁は、先頭のゼロが無視されます（つまり、先行ゼロをスキップできます）。書式モデル内で指定された 9 の数よりも入力文字列の桁数が多い場合、エラーになります。
“`sql
— 基本的な使用
SELECT TO_NUMBER(‘123’, ‘999’); — 結果: 123
SELECT TO_NUMBER(‘ 123’, ‘999’); — 結果: 123 (先頭の空白は無視されることが多い)

— 先行ゼロの扱い (9は先行ゼロを無視できる)
SELECT TO_NUMBER(‘00123’, ‘999’); — 結果: 123
SELECT TO_NUMBER(‘00123’, ‘99999’); — 結果: 123

— 書式モデルより入力が多い場合 (エラー)
— SELECT TO_NUMBER(‘1234’, ‘999’); — エラー: invalid input syntax for type numeric: “1234”
“`
0: 数値を表します。入力文字列中の数字に対応します。9 と異なり、書式モデル内で指定された位置に数字がない場合、結果にはゼロが埋められます。これにより、固定幅の数値文字列を扱う際に先行ゼロや末尾ゼロを維持できます。
“`sql
— 基本的な使用
SELECT TO_NUMBER(‘123’, ‘000’); — 結果: 123
SELECT TO_NUMBER(‘012’, ‘000’); — 結果: 12

— 先行ゼロの維持
SELECT TO_NUMBER(‘123’, ‘00000’); — 結果: 123 (入力に0が足りない場合は埋められない。0はあくまで入力に数字があるかをチェックし、出力の桁数を保証するために使われる)
— 0は主にTO_CHARで出力を整形する際に先行ゼロを埋めるために使われるが、
— TO_NUMBERでも入力文字列の桁数チェックに使われる。
— TO_NUMBER(’12’, ‘000’) は ’12’ に対応する。書式 ‘000’ は3桁を期待する。
— 実際にはTO_NUMBERにおける’0’は、書式モデル中の対応する位置に数字があることを期待し、
— 無ければエラーか、無視される。PostgreSQLのドキュメントでは’9’と似た挙動をするが、
— ‘0’は固定幅のフォーマットで入力が期待される桁数と一致することを強調する意味合いが強い。
— むしろ、最も重要な違いは、TO_CHARでのゼロ埋め挙動にある。TO_NUMBERにおいては、
— ‘9’で十分なことが多いが、厳密な桁数チェックを意図する場合は’0’も使用される。

— 例外的なケースとして、小数点以下の末尾ゼロを維持する場合（TO_CHARの挙動に近い）
— SELECT TO_NUMBER(‘12.30’, ‘99.00’); — 結果: 12.30 (ただし、これはNUMERICの内部表現であり、表示形式ではない)

— 実際のTO_NUMBERでの ‘0’ の使用例としては、固定幅の数値フィールドで、
— 入力が期待される桁数を持っているかを確認するのに役立つ。
SELECT TO_NUMBER(‘0123’, ‘0000’); — 結果: 123
— SELECT TO_NUMBER(‘123’, ‘0000’); — エラー: invalid input syntax for type numeric: “123” (入力が4桁ではないため)
`` *補足*:0と9の主な違いは、TO_CHAR関数で数値を文字列に変換する際に出力される先行ゼロの扱いにあります。TO_NUMBERにおいては、どちらも入力文字列中の数字に対応しますが、0を使うことで入力文字列が期待される桁数を持っているかをより厳密にチェックする意図が込められることが多いです。しかし、9は先行ゼロを無視してマッチできるため、柔軟性があります。通常、9を主に使用し、特定のケースでのみ0` を検討すれば十分です。

4.2. 小数点と桁区切り

. (ピリオド): 小数点を表します。書式モデル中で一度だけ使用できます。入力文字列中の小数点を区切る文字（通常はピリオドまたはコンマ、ロケールによる）に対応します。
sql SELECT TO_NUMBER('123.45', '999.99'); -- 結果: 123.45 SELECT TO_NUMBER('123,45', '999.99'); -- ロケールによってはエラーになる可能性がある（カンマを小数点と認識しないロケールの場合）
, (コンマ): 桁区切り（グループセパレーター）を表します。書式モデル中の任意の位置に複数回使用できます。入力文字列中の桁区切り文字（通常はコンマまたはピリオド、ロケールによる）に対応します。9 や 0 の間に配置されます。
sql SELECT TO_NUMBER('1,234', '9,999'); -- 結果: 1234 SELECT TO_NUMBER('1,234.56', '9,999.99'); -- 結果: 1234.56 SELECT TO_NUMBER('1,234,567', '9,999,999'); -- 結果: 1234567
注意: ピリオド (.) とコンマ (,) の役割は、使用されているロケール (LC_NUMERIC 設定) によって入れ替わることがあります。例えば、ヨーロッパの多くの国ではコンマが小数点、ピリオドが桁区切りとして使われます。

4.3. ロケール独立の小数点と桁区切り

D: ロケール依存の小数点を表します。書式モデル中で一度だけ使用できます。LC_NUMERIC 設定で定義された小数点文字（例: ‘.’ または ‘,’）に対応します。D を使用することで、特定のロケール設定に依存しない書式を作成できます。. の代わりにこちらを使うことが推奨される場合が多いです。
“`sql
— ロケール設定がen_US (小数点: .) の場合
SELECT TO_NUMBER(‘123.45’, ‘999D99’); — 結果: 123.45

— ロケール設定がfr_FR (小数点: ,) の場合
— 同じクエリを実行すると
— SELECT TO_NUMBER(‘123,45’, ‘999D99’); — 結果: 123.45 (LC_NUMERIC=fr_FRならコンマが小数点と認識される)
— SELECT TO_NUMBER(‘123.45’, ‘999D99’); — エラー (LC_NUMERIC=fr_FRならピリオドは小数点と認識されない)
“`
G: ロケール依存の桁区切りを表します。書式モデル中の任意の位置に複数回使用できます。LC_NUMERIC 設定で定義された桁区切り文字（例: ‘,’ または ‘.’）に対応します。, の代わりにこちらを使うことが推奨される場合が多いです。
“`sql
— ロケール設定がen_US (桁区切り: ,) の場合
SELECT TO_NUMBER(‘1,234’, ‘9G999’); — 結果: 1234

— ロケール設定がfr_FR (桁区切り: .) の場合
— 同じクエリを実行すると
— SELECT TO_NUMBER(‘1.234’, ‘9G999’); — 結果: 1234 (LC_NUMERIC=fr_FRならピリオドが桁区切りと認識される)
— SELECT TO_NUMBER(‘1,234’, ‘9G999’); — エラー (LC_NUMERIC=fr_FRならコンマは桁区切りと認識されない)
``DとG` を使用することで、実行環境のロケール設定に合わせた小数点と桁区切り文字を自動的に認識させることができます。異なるロケール設定を持つ環境で同じスクリプトを実行する場合に役立ちます。

4.4. 通貨記号

$: 入力文字列中のドル記号 ($) に対応します。書式モデル中の任意の位置に一度だけ使用できます。
sql SELECT TO_NUMBER('$123.45', '$999.99'); -- 結果: 123.45 SELECT TO_NUMBER('123.45$', '999.99$'); -- 結果: 123.45
L: ロケール依存の通貨記号を表します。LC_MONETARY 設定で定義された通貨記号に対応します。書式モデル中の任意の位置に一度だけ使用できます。
“`sql
— ロケール設定がen_US (通貨記号: $) の場合
SELECT TO_NUMBER(‘$123.45’, ‘L999.99’); — 結果: 123.45

— ロケール設定がja_JP.UTF-8 (通貨記号: ¥) の場合
— 同じクエリを実行すると
— SELECT TO_NUMBER(‘¥123’, ‘L999’); — 結果: 123 (LC_MONETARY=ja_JP.UTF-8なら円記号が認識される)
``L` を使用することで、異なる通貨記号を持つ文字列を、ロケール設定に応じて柔軟に変換できます。

4.5. 符号表示

S: 入力文字列中の符号 (+ または -) に対応します。書式モデルの先頭または末尾に配置できます。この要素を使用すると、結果の数値に符号が含まれます。
“`sql
SELECT TO_NUMBER(‘+123’, ‘S999’); — 結果: 123
SELECT TO_NUMBER(‘-456’, ‘S999’); — 結果: -456
SELECT TO_NUMBER(‘123+’, ‘999S’); — 結果: 123
SELECT TO_NUMBER(‘456-‘, ‘999S’); — 結果: -456

— Sがない場合、符号付き文字列はエラーになることが多い（ロケールによる）
— SELECT TO_NUMBER(‘+123’, ‘999’); — エラー (符号は予期されない文字)
“`
MI: 入力文字列中のマイナス符号 (-) に対応します。書式モデルの末尾に配置できます。S とは異なり、プラス符号には対応しません。
sql SELECT TO_NUMBER('123-', '999MI'); -- 結果: -123 -- SELECT TO_NUMBER('123+', '999MI'); -- エラー ('+' は予期されない文字)
PR: 入力文字列中の括弧 () に対応します。負の数を表すためによく使用されます。書式モデルの先頭または末尾に配置できます。
sql SELECT TO_NUMBER('(123)', 'PR999'); -- 結果: -123 SELECT TO_NUMBER(' (123) ', 'PR999'); -- 結果: -123 (空白は無視される) SELECT TO_NUMBER('123', 'PR999'); -- 結果: 123 (括弧がない場合は正の数として扱われる)
PL: 入力文字列中のプラス符号 (+) に対応します。書式モデルの先頭または末尾に配置できます。MI とは異なり、マイナス符号には対応しません。
sql SELECT TO_NUMBER('+123', 'PL999'); -- 結果: 123 -- SELECT TO_NUMBER('-123', 'PL999'); -- エラー ('-' は予期されない文字)
SG: ロケール依存の符号表示に対応します。LC_NUMERIC 設定で定義された符号位置と文字（通常、先頭または末尾の +/-）に対応します。書式モデルの先頭または末尾に配置できます。S と同様の機能ですが、ロケールに依存します。
sql -- ロケール設定がen_US (符号: 先頭の + / -) の場合 SELECT TO_NUMBER('+123', 'SG999'); -- 結果: 123 SELECT TO_NUMBER('-456', 'SG999'); -- 結果: -456

4.6. 指数表記

EEEE: 入力文字列中の指数表記（例: e+NN, E-NN など）に対応します。書式モデルの末尾に配置する必要があります。
sql SELECT TO_NUMBER('1.23e+04', '9D99EEEE'); -- 結果: 12300 SELECT TO_NUMBER('4.56E-02', '9D99EEEE'); -- 結果: 0.0456 SELECT TO_NUMBER('123E4', '999EEEE'); -- 結果: 1230000
EEEE は、入力文字列中の e または E に続く符号および指数部分全体を処理します。

4.7. その他の書式要素

B: 入力文字列中の先行または末尾の空白を無視します。TO_NUMBER はデフォルトで先行/末尾の空白を無視する傾向がありますが、この要素を明示的に使用することで意図を明確にできます。
sql SELECT TO_NUMBER(' 123 ', 'B999B'); -- 結果: 123 -- 通常、Bなしでも同じ結果になることが多い SELECT TO_NUMBER(' 123 ', '999'); -- 結果: 123
TM: 入力文字列中の先行または末尾の空白、および書式モデルで指定されていない任意の非数値文字を無視します。非常に柔軟ですが、意図しない変換を引き起こす可能性もあるため注意が必要です。通常は TM9 や TM9.9 のように使用されます。
sql -- SELECT TO_NUMBER(' $ 1,234.56 ', 'TM9'); -- これは機能しない。TMは単独では使えない。 -- TMは他の数値書式要素と組み合わせて使われる。 -- TMの最も一般的な使い方は、TO_CHARの出力形式（TM9など）をTO_NUMBERでパースする場合。 -- TO_NUMBER('123', 'TM'); -- エラー -- TO_NUMBER('123', 'TM9'); -- 結果: 123 -- TO_NUMBER('$123', 'TM9'); -- 結果: 123 (TMが'$'を無視) -- SELECT TO_NUMBER(' $ 1,234.56 ', 'TM9G999D99'); -- 結果: 1234.56
TM は、入力文字列に予測不能な非数値文字が含まれる可能性がある場合に役立ちますが、可能な限り具体的な書式要素を使用することが推奨されます。
FM (Fill Mode): これは主に TO_CHAR で使用される書式要素であり、結果文字列の先行/末尾の空白を抑制したり、固定幅を解除したりする働きをします。TO_NUMBER において FM を指定してもエラーにはなりませんが、入力文字列の解析においては通常無視されます。入力文字列の空白や非数値文字の扱いは、他の書式要素やPostgreSQLのデフォルトの解析ルールによって決定されます。
sql -- TO_NUMBERではFMの効果はほぼない SELECT TO_NUMBER(' 123 ', 'FM999'); -- 結果: 123 SELECT TO_NUMBER('123', 'FM999'); -- 結果: 123 -- 上記はFMなしの場合と同じ結果になる
C: ロケール依存のISO通貨記号に対応します。LC_MONETARY 設定で定義された3文字の通貨コード（例: ‘USD’, ‘JPY’）に対応します。書式モデルの先頭または末尾に配置できます。
sql -- ロケール設定がen_US (ISO通貨記号: USD) の場合 SELECT TO_NUMBER('USD 123.45', 'C999.99'); -- 結果: 123.45
U: ロケール依存のユーロ記号 (€) に対応します。書式モデルの先頭または末尾に配置できます。
sql -- ロケール設定によっては (€ が LC_MONETARY で定義されている場合) SELECT TO_NUMBER('€123.45', 'U999.99'); -- 結果: 123.45
RN: ローマ数字（大文字）に対応します。入力文字列がローマ数字の場合に使用します。
sql SELECT TO_NUMBER('XIV', 'RN'); -- 結果: 14 SELECT TO_NUMBER('XIX', 'RN'); -- 結果: 19 SELECT TO_NUMBER('MMXXIII', 'RN'); -- 結果: 2023
入力文字列は、書式モデル全体が RN で構成されている必要があります（例: 'RN' のみ）。他の数値要素と組み合わせることはできません。
RM: ローマ数字（小文字）に対応します。入力文字列がローマ数字（大文字または小文字）の場合に使用します。
sql SELECT TO_NUMBER('xiv', 'RM'); -- 結果: 14 SELECT TO_NUMBER('Xix', 'RM'); -- 結果: 19 SELECT TO_NUMBER('mmxxiii', 'RM'); -- 結果: 2023
RM も書式モデル全体で構成される必要があります。
TH または th: 序数接尾辞（英語の “th”, “st”, “nd”, “rd”）に対応します。TO_NUMBER では、これらの接尾辞は無視されます。主に TO_CHAR で数値を序数付き文字列に変換する際に使用されます。TO_NUMBER の書式モデルに含めてもエラーにはなりませんが、入力文字列にこれらの接尾辞が含まれていても数値変換には影響しません（接尾辞が存在しない場合もエラーにはなりません）。
sql -- TH/thはTO_NUMBERでは無視される SELECT TO_NUMBER('1st', '9TH'); -- 結果: 1 (THは無視される) SELECT TO_NUMBER('2nd', '9TH'); -- 結果: 2 SELECT TO_NUMBER('3rd', '9TH'); -- 結果: 3 SELECT TO_NUMBER('4th', '9TH'); -- 結果: 4 SELECT TO_NUMBER('11th', '99TH'); -- 結果: 11 SELECT TO_NUMBER('21st', '99TH'); -- 結果: 21

4.8. 書式モデルの組み合わせルールと考慮事項

数字プレースホルダー: 9 や 0 は、整数部と小数点以下の両方で使用できます。. や D で区切ります。
小数点と桁区切り: . または D は書式モデル中に一つだけです。, または G は複数使用できます。./, と D/G を混在させるべきではありません。ロケール依存が必要なら D/G のみを使用し、ロケール非依存の厳密な書式が必要なら ./, のみを使用します。
符号: S, MI, PL, SG, PR は、書式モデルの先頭または末尾に配置できます。複数指定するとエラーになるものや、特定の組み合わせが許可されないものがあります（例: MI と PL は同時に使えない）。PR は他の符号要素と組み合わせることも可能ですが、通常は単独で使われます。
通貨: $, L, C, U は、書式モデルの先頭または末尾に配置できます。複数指定や異なる通貨要素の混在はエラーになる可能性があります。
空白: B は先頭と末尾に指定できます。TM はより広範な非数値文字を無視しますが、その挙動は他の要素との組み合わせによって変わる可能性があります。
TM の注意点: TM は非常に柔軟ですが、予期しない文字まで無視してしまう可能性があるため、可能な限り具体的な書式モデルを使用することが推奨されます。TM は他の要素と組み合わせて使用する必要があります（例: 'TM9', 'TM9.9'）。
厳密な一致: TO_NUMBER は、入力文字列が書式モデルと厳密に一致することを期待します。書式モデルにない文字が入力文字列に含まれている場合や、数字プレースホルダーに対応する位置に数字以外の文字がある場合（符号や小数点を除く）、または桁数が大きく異なる場合などにエラーが発生します。ただし、先行/末尾の空白は通常無視されます。
書式要素の順序: 一部の要素（S, PR, $, L, C, U, B, TM, SG, PL, MI, EEEE）は、書式モデルの先頭または末尾にしか置けません。9 と 0 は数値部分を構成し、./, または D/G で小数点前後が区切られます。EEEE は必ず数値部分の後に来ます。

これらの書式要素を理解し、適切に組み合わせることで、多種多様な形式の数値文字列を TO_NUMBER で正確に変換することが可能になります。

5. ロケールとTO_NUMBER関数

前述の通り、LC_NUMERIC および LC_MONETARY というPostgreSQLのロケール設定は、./, および $ のような書式要素の解釈に影響を与えます。

LC_NUMERIC: 小数点文字 (decimal_point) と桁区切り文字 (thousands_sep) を定義します。デフォルトでは . が小数点、, が桁区切りですが、ロケールによってはこれが逆になります（例: fr_FR）。
- 書式モデルで . と , を使用した場合、それらは 現在の LC_NUMERIC 設定 に従って小数点または桁区切りとして解釈されます。
- 書式モデルで D と G を使用した場合、D は常に LC_NUMERIC の decimal_point に対応し、G は常に LC_NUMERIC の thousands_sep に対応します。これにより、コードの可搬性が高まります。
LC_MONETARY: 通貨記号 (currency_symbol) やその表示方法、ISO通貨記号 (int_curr_symbol) などを定義します。
- 書式モデルで $ を使用した場合、それは常にリテラルのドル記号 ($) に対応します。
- 書式モデルで L を使用した場合、それは 現在の LC_MONETARY 設定 の currency_symbol に対応します。
- 書式モデルで C を使用した場合、それは 現在の LC_MONETARY 設定 の int_curr_symbol に対応します。

したがって、TO_NUMBER を使用する際は、クエリを実行するPostgreSQLセッションのロケール設定に注意が必要です。特に . や ,、$ を書式モデルで使用する場合は、意図した通りの解釈がされるか確認する必要があります。

複数のロケール環境で動作するシステムや、外部データのロケールが不明確な場合は、ロケール依存の書式要素 (D, G, L, C, U, SG) を積極的に使用するか、あるいは入力文字列を正規化する前処理を検討する方が安全です。

現在のセッションのロケール設定は、以下のコマンドで確認できます。
sql SHOW LC_NUMERIC; SHOW LC_MONETARY;
これらの設定は、SET LC_NUMERIC = ...; や SET LC_MONETARY = ...; コマンドで変更できますが、これは現在のセッションにのみ影響します。データベース全体のデフォルトを変更するには、postgresql.conf ファイルを編集するか、ALTER SYSTEM SET ... コマンドを使用する必要があります。

6. TO_NUMBER関数の戻り値の型

TO_NUMBER 関数は、特別な指定がない限り NUMERIC 型の値を返します。NUMERIC 型は任意精度をサポートしており、小数点以下の桁数や全体の桁数が非常に大きい場合でも精度を失わずに数値を表現できます。

例えば、書式モデル '999.99' を使用して '123.45' を変換した場合、結果は NUMERIC(5, 2) のような精度・位取りを持つ NUMERIC 値になります（ただし、PostgreSQLはデフォルトでは精度や位取りを明示的に制限せず、必要に応じて動的に調整します）。

もし変換結果を特定の整数型 (INTEGER, BIGINT) や浮動小数点型 (REAL, DOUBLE PRECISION) にしたい場合は、TO_NUMBER の結果をさらにキャストする必要があります。

“`sql
SELECT TO_NUMBER(‘123’, ‘999’)::INTEGER;
— 結果: 123 (INTEGER型)

SELECT TO_NUMBER(‘123.45’, ‘999.99’)::DOUBLE PRECISION;
— 結果: 123.45 (DOUBLE PRECISION型)
“`

ただし、小数部分を持つ NUMERIC 値を整数型にキャストすると、小数部分が切り捨てられることに注意してください。

sql SELECT TO_NUMBER('123.78', '999.99')::INTEGER; -- 結果: 123 (INTEGER型、小数点以下は切り捨て)
必要に応じて、ROUND() 関数などで丸めてからキャストすることも検討してください。

7. エラー処理

TO_NUMBER 関数は、入力文字列が指定された format 書式モデルと一致しない場合にエラーを発生させます。これは、データの品質問題を即座に特定できるという利点がある一方で、不正な形式の文字列が混在するデータを扱う際には注意が必要です。

エラーが発生する主なケースは以下の通りです。

書式モデルにない文字が含まれている: 入力文字列に、書式モデルのどの要素にも対応しない文字が含まれている場合。
sql SELECT TO_NUMBER('123ABC', '999'); -- エラー: invalid input syntax for type numeric: "123ABC" (ABCが不正) SELECT TO_NUMBER('123 456', '999'); -- エラー (空白は通常無視されるが、途中の空白はエラーになることが多い)
ただし、書式モデルの先頭や末尾にある先行/末尾の空白は通常無視されます。
数字プレースホルダーに対応する位置に数字以外の文字がある: 9 や 0 が期待される位置に、数字、小数点、桁区切り、符号以外の文字がある場合。
sql SELECT TO_NUMBER('12A', '999'); -- エラー: invalid input syntax for type numeric: "12A" (Aが不正)
桁区切りや小数点が多い/少ない: . や D が複数回出現したり、書式モデル中の . や D の数と入力文字列中の小数点文字の数が一致しない場合。桁区切りについても同様。
sql SELECT TO_NUMBER('1.2.3', '9.9.9'); -- エラー (小数点が多い) SELECT TO_NUMBER('1,2,3', '9,9,9'); -- エラー (桁区切りが多すぎるか位置が不正)
入力文字列の桁数が書式モデルの桁数を超える: 整数部や小数部の桁数が、書式モデルで指定された 9 や 0 の数を超える場合。
sql SELECT TO_NUMBER('12345', '999'); -- エラー (整数部の桁が多すぎる) SELECT TO_NUMBER('1.2345', '9.99'); -- エラー (小数部の桁が多すぎる)
符号や通貨記号の位置/有無が一致しない: 書式モデルに符号や通貨記号が含まれているのに、入力文字列にそれがなかったり、位置が異なったりする場合。逆も同様（書式モデルにない符号/通貨記号が入力文字列にある場合）。
sql SELECT TO_NUMBER('-123', '999'); -- エラー (符号は予期されない) SELECT TO_NUMBER('123', 'S999'); -- エラー (Sが期待されたが入力にない) SELECT TO_NUMBER('$123', '999'); -- エラー ($は予期されない)

不正なデータが混在するテーブルに対して TO_NUMBER を含むクエリを実行すると、最初の不正な行でクエリ全体が失敗します。このような場合、エラーを回避するには、変換の前に不正なデータをフィルタリングする必要があります。

例えば、LIKE 演算子や正規表現を使って、変換可能な形式の文字列のみを選択してから TO_NUMBER を適用する方法があります。

“`sql
— 例: 数字と小数点のみを含む文字列のみを変換
SELECT TO_NUMBER(string_column, ‘999.99’)
FROM your_table
WHERE string_column ~ ‘^[0-9].?[0-9]$’; — 基本的な数値形式の正規表現

— 例: カンマ区切りやドル記号を含む可能性のある文字列の場合
SELECT TO_NUMBER(string_column, ‘S$9,999.99’)
FROM your_table
WHERE string_column ~ ‘^[+-]?\$?[0-9,].?[0-9]$’; — より複雑な形式に対応する正規表現（完璧ではないが例として）
“`

この方法では、フィルタリング条件の正規表現が複雑になりがちで、TO_NUMBER の書式モデルとの整合性を保つのが難しいという欠点があります。

別のアプローチとして、アプリケーション側でエラーを捕捉して処理する方法や、PostgreSQL 13以降で利用可能な pg_try_cast() 関数（標準の CAST にのみ適用可能で、TO_NUMBER には直接使えない）のような機能拡張（もし将来的に TO_NUMBER にも対応するようなものが出れば）を検討することもできます。しかし、現状の標準的なPostgreSQLで、TO_NUMBER のエラーを個別の行で捕捉してスキップしたりNULLにしたりする直接的なSQL関数はありません。最も確実なのは、事前にデータの形式を検証するか、エラーが発生することを許容してアプリケーション側で適切に処理することです。

8. TO_NUMBERとCAST/::演算子の比較

TO_NUMBER と CAST/:: 演算子はいずれも文字列を数値に変換しますが、その機能と得意な場面が異なります。

CAST/::演算子:

シンプル: 標準的な数値形式（符号、数字、小数点、指数）の文字列を変換するのに最もシンプルで直接的な方法です。
sql SELECT '123'::INTEGER; SELECT '456.78'::NUMERIC; SELECT '-1.23e+5'::DOUBLE PRECISION;
高速: 書式解析のオーバーヘッドがないため、通常は TO_NUMBER より高速です。
柔軟性がない: カンマ（桁区切り）、通貨記号、括弧による負数表現など、非標準的な書式には対応できません。エラーになります。
sql -- 失敗例 -- SELECT '1,234'::NUMERIC; -- エラー -- SELECT '$100'::NUMERIC; -- エラー -- SELECT '(50)'::NUMERIC; -- エラー

TO_NUMBER関数:

柔軟: 指定された format 書式モデルに従って、多様な形式の数値文字列を解析できます。桁区切り、通貨記号、様々な符号表示、指数表記などに対応できます。
高精度: 戻り値がデフォルトで NUMERIC 型であるため、精度を損なわずに変換できます。
書式モデルが必要: 変換には入力文字列の書式を正確に指定する必要があります。
オーバーヘッド: 書式解析の処理が必要なため、シンプルキャストに比べてわずかにオーバーヘッドがあります（ただし、ほとんどのユースケースでは無視できる差です）。
エラーがより頻繁: format モデルとの不一致によってエラーが発生しやすいため、データ形式が不均一な場合にはエラー処理がより重要になります。

どちらを使うべきか？

入力文字列が常に標準的な数値形式であることが保証されている場合は、シンプルで高速な CAST または :: 演算子を使用するのが適切です。
入力文字列が、カンマ区切り、通貨記号、括弧による負数など、標準以外の特定の書式を持っている場合は、TO_NUMBER を使用する必要があります。

多くの場合、外部からのインポートデータや、人間が入力・編集した可能性のあるデータには TO_NUMBER が、システム内部で生成された厳密な形式の数値文字列には CAST が適しています。

9. 実際の応用例

TO_NUMBER 関数は、様々なデータ処理シナリオで役立ちます。

9.1. CSVファイルからのインポート

桁区切りカンマや通貨記号を含むCSVファイルをインポートする際に、COPY コマンドと組み合わせて TO_NUMBER を使用できます。

例えば、以下のようなCSVファイルがあるとします。
csv product_name,price_usd,stock_count "Laptop","$1,200.50",150 "Mouse","$25.99",500 "Keyboard","$75.00",220 "Monitor","$300.00",80
この price_usd 列は文字列型で、$, ,, . が含まれています。これを数値型でテーブルに格納するには、以下のような COPY 文と TO_NUMBER を組み合わせた INSERT 文を使用できます。

まず、テーブルを作成します。
sql CREATE TABLE products ( product_name VARCHAR(255), price_usd NUMERIC, stock_count INTEGER );
次に、CSVファイルをインポートします。COPY コマンドで直接変換するのは難しいため、一時テーブルに文字列として読み込んでから TO_NUMBER で変換して挿入するのが一般的です。

“`sql
— 一時テーブルを作成
CREATE TEMPORARY TABLE temp_products (
product_name TEXT,
price_usd_text TEXT,
stock_count_text TEXT
);

— CSVファイルを一時テーブルにコピー
COPY temp_products FROM ‘/path/to/your/products.csv’ WITH (FORMAT CSV, HEADER true);

— TO_NUMBERを使って変換し、本来のテーブルに挿入
INSERT INTO products (product_name, price_usd, stock_count)
SELECT
product_name,
TO_NUMBER(price_usd_text, ‘S$9,999.99’), — 通貨記号、桁区切り、小数点、符号に対応
stock_count_text::INTEGER — stock_countはシンプルな数値形式と仮定しキャスト
FROM temp_products;

— 一時テーブルを削除
DROP TABLE temp_products;
`` この例では、‘S$9,999.99’という書式モデルでprice_usd_textを解析しています。Sは符号（+または–）に対応（ここでは入力にありませんが安全のため）、$はドル記号、9,999は桁区切りカンマを含む整数部、.99` は小数点以下2桁に対応します。

9.2. 不均一な形式のデータクリーニング

ユーザー入力やレガシーシステムからのデータなど、同じ列なのに数値形式が複数混在している場合があります。例えば、ある行では '1,234.56'、別の行では '1234.56'、さらに別の行では ' $ 1234.56' のようになっている場合です。

このような場合、複数の TO_NUMBER 呼び出しや条件分岐（CASE文）を組み合わせることで対応できる可能性がありますが、書式モデルに合わない場合はエラーになるため、事前にデータのパターンをある程度把握し、対応する書式モデルを定義する必要があります。あるいは、正規表現で特定のパターンを抽出してから TO_NUMBER に渡す、といったより複雑な処理が必要になることもあります。

例：カンマあり/なし、ドル記号あり/なしが混在する場合

sql -- 形式を推測して変換を試みる（エラー回避は別途考慮が必要） SELECT CASE WHEN string_column LIKE '%,%' THEN TO_NUMBER(string_column, '9,999.99') -- カンマ形式 WHEN string_column LIKE '$%' THEN TO_NUMBER(string_column, '$999.99') -- ドル記号形式 (カンマなし) ELSE TO_NUMBER(string_column, '999.99') -- シンプル形式 END as numeric_value FROM your_table;
この例は単純化されており、実際にはより多くのパターンやエラー処理（WHERE 句でフィルタリングするなど）が必要になります。重要なのは、TO_NUMBER が異なる書式モデルを適用できる柔軟性を持っているという点です。

9.3. 集計と分析の前処理

文字列として格納された数値列に対して集計（合計、平均など）や数値に基づく分析を行うには、まずそれらを数値に変換する必要があります。TO_NUMBER は、この前処理ステップで中心的な役割を果たします。

sql -- 文字列として格納された売上データ（例: '$1,234.50', '$500.00', '( $ 75.25 )'） SELECT SUM(TO_NUMBER(sales_text, 'S$9,999.99PR')) FROM sales_table;
この例では、S$9,999.99PR という書式モデルで、符号、ドル記号、桁区切り、小数点、そして括弧による負数表現に対応しています。

10. パフォーマンスに関する考慮事項

TO_NUMBER 関数は、CAST/:: 演算子に比べてわずかに実行計画のオーバーヘッドが発生する可能性があります。これは、書式モデルの解析と、それに基づく入力文字列の文字ごとのチェックが必要になるためです。

しかし、一般的なOLTP（オンライントランザクション処理）やOLAP（オンライン分析処理）のクエリにおいて、このオーバーヘッドがボトルネックになることは稀です。変換対象のデータ量が膨大で、かつ変換がクエリの大部分を占めるような特殊なシナリオ（例: 数千万行以上の文字列数値列に対する一括変換）でない限り、パフォーマンス差は無視できるレベルであることがほとんどです。

パフォーマンスが懸念される場合は、以下の点を考慮してください。

インデックス: 文字列列にインデックスがあっても、TO_NUMBER 関数を適用するとインデックスが利用できなくなる可能性があります。もし変換後の数値に基づいて頻繁に検索や結合を行う場合は、変換後の数値を格納する新しい数値型列を追加し、そちらにインデックスを作成することを検討してください。
ETLプロセス: 大量のデータを変換する場合、クエリ内で都度 TO_NUMBER を呼び出すのではなく、ETL（抽出、変換、ロード）プロセスの一部としてデータをロードする際に一度だけ変換し、クリーンな数値型としてデータベースに格納するのが最も効率的です。一時テーブルを使ったインポート方法などがこれに該当します。

結論として、TO_NUMBER のパフォーマンスについて過度に心配する必要はありません。その機能的な柔軟性が、ほとんどの場合、わずかなパフォーマンスの差を上回る価値を提供します。

11. TO_NUMBERを使う上でのベストプラクティス

TO_NUMBER 関数を効果的かつ安全に使用するために、いくつかのベストプラクティスを推奨します。

書式モデルを正確に把握する: 変換したい入力文字列がどのような形式で来るかを事前にしっかりと確認し、それに合致する書式モデルを定義してください。不明瞭な形式のデータが混在する場合は、複数の書式モデルや前処理（正規表現によるパターンマッチングなど）を検討してください。
ロケール設定に注意する: ., ,, $ といった記号を書式モデルに使う場合は、実行環境の LC_NUMERIC や LC_MONETARY 設定が意図と合っているか確認してください。異なるロケール環境での可搬性を考慮する場合は、D, G, L, C, U, SG のようなロケール依存の書式要素を使用することを検討してください。
エラー処理を考慮する: TO_NUMBER はエラーを発生させる可能性があるため、変換対象のデータに不正な形式のものが含まれる可能性がある場合は、必ずエラー処理の方法を計画してください。最も標準的な方法は、WHERE 句で有効な形式のデータのみを選択してから変換を行うことです。
可能な限り厳密な書式モデルを使う: TM のように非常に柔軟な書式要素は便利ですが、予期しない文字まで無視してしまうリスクがあります。可能な限り具体的な書式要素（9, 0, $, ,, ., PR など）を組み合わせて、変換したいパターンを正確に記述することが推奨されます。これにより、意図しないデータが紛れ込んだ際にエラーとして検知しやすくなります。
テストを行う: 様々な有効な入力パターンと、無効な（エラーになるはずの）入力パターンを用意して、TO_NUMBER の呼び出しを十分にテストしてください。特に、境界値や例外的な書式（例: 符号の位置、小数点以下のゼロなど）を確認することが重要です。
結果の型を検討する: TO_NUMBER はデフォルトで NUMERIC を返しますが、必要に応じてさらに特定の数値型（INTEGER, DOUBLE PRECISIONなど）にキャストしてください。ただし、キャストによる情報の損失（小数部分の切り捨てなど）がないか注意してください。
コードをコメントで補足する: 使用している書式モデルがなぜそのようになっているのか、どのような入力形式を想定しているのかをコメントで記述しておくと、将来的なメンテナンスが容易になります。

12. まとめ

PostgreSQLの TO_NUMBER 関数は、文字列として格納された数値を、多様な書式パターンを解釈しながら高精度な数値型（デフォルトは NUMERIC）に変換するための強力で柔軟なツールです。シンプルキャスト (::/CAST) が標準的な数値形式にしか対応しないのに対し、TO_NUMBER は桁区切り、通貨記号、様々な符号表示、指数表記、ローマ数字など、より複雑な形式の文字列を扱うことができます。

その能力の核心は、入力文字列の解析ルールを定義する format 書式モデルにあります。9, 0 といった数字プレースホルダーに加え、., ,, D, G といった小数点/桁区切り、$, L, C, U といった通貨記号、S, MI, PL, SG, PR といった符号表示、EEEE といった指数表記など、豊富な書式要素を組み合わせることで、様々な現実世界の数値表現に対応できます。

一方で、TO_NUMBER は入力文字列が指定された書式モデルと厳密に一致することを期待するため、不一致の場合はエラーを発生させます。不均一なデータソースを扱う際には、変換前にデータをフィルタリングするか、エラー処理を適切に設計することが不可欠です。

データインポート、データクリーニング、分析のための前処理など、文字列形式の数値データを扱う多くのシナリオで TO_NUMBER は中心的な役割を果たします。この記事で詳述した書式要素、ロケールの影響、エラー処理、そしてベストプラクティスを理解することで、PostgreSQLにおける数値データ処理の幅が大きく広がるはずです。

複雑な数値文字列の変換に直面した際は、ぜひ TO_NUMBER 関数の活用を検討し、この記事をその手助けとしてご活用ください。 PostgreSQLの公式ドキュメントも合わせて参照することで、さらに深い理解を得られるでしょう。

これで、PostgreSQLのTO_NUMBER関数に関する、詳細で約5000語の記事が完成しました。書式モデルの各要素、ロケールの影響、エラー処理、応用例、比較など、様々な側面を網羅的に解説しました。