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

By /

PostgreSQLにおける文字列から数値への変換方法：徹底解説

はじめに

データベースシステムにおいて、データの型を適切に管理することは非常に重要です。特に、文字列として格納されているデータを数値として扱いたい場面は頻繁に発生します。例えば、ユーザーからの入力データ、外部システムからのインポートデータ、あるいは過去のシステムから引き継いだデータなどが文字列として格納されているものの、実際には金額、数量、スコア、IDといった数値として計算や比較を行いたい場合があります。

PostgreSQLは、堅牢で高機能なリレーショナルデータベースシステムであり、さまざまなデータ型間の変換機能を提供しています。文字列を数値に変換する方法も複数用意されており、それぞれの方法には利点と欠点があります。どの方法を選択するかは、変換したい文字列の形式、エラー発生時の挙動、PostgreSQLのバージョン、そしてパフォーマンス要件によって異なります。

この記事では、PostgreSQLで文字列を数値に変換するための主要な方法を、具体的なSQLコード例を交えながら詳細に解説します。基本的なキャストから、エラーハンドリング、複雑な書式を持つ文字列の変換、そしてパフォーマンスに関する考慮事項まで、網羅的に説明することを目指します。

この記事を読むことで、以下のことができるようになります。

  • PostgreSQLにおける基本的な型変換（キャスト）の方法を理解する。
  • 文字列を整数型、浮動小数点型、精度指定可能な数値型に変換する方法を知る。
  • 変換時に発生しうるエラーの種類とその対処方法を学ぶ。
  • TRY_CAST を使用してエラーを回避する方法を理解する（PostgreSQL 13以降）。
  • to_number 関数を使用して、書式指定やロケールを考慮した高度な変換を行う方法を習得する。
  • 変換処理におけるパフォーマンスの注意点を把握する。
  • 実践的な応用例やよくあるトラブルシューティングについて学ぶ。

それでは、PostgreSQLでの文字列から数値への変換の世界に入りましょう。

1. 基本的な変換方法：キャスト

PostgreSQLで最も一般的でシンプルな文字列から数値への変換方法は、「キャスト」と呼ばれる機能を使用することです。キャストには主に2つの構文があります。

  • キャスト演算子 (::)
  • CAST() 関数

これらは機能的にはほぼ同じですが、構文と標準SQLへの準拠度合いに違いがあります。

1.1 キャスト演算子 (::)

キャスト演算子 (::) は、PostgreSQLで広く使われている独自の構文です。非常に簡潔に型変換を記述できます。

構文:
sql
'文字列'::対象の数値型

例：文字列 '123' を整数型 (INTEGER) に変換する
sql
SELECT '123'::INTEGER;
結果:
“`
integer

 123

“`

例：文字列 '3.14' を浮動小数点型 (DOUBLE PRECISION) に変換する
sql
SELECT '3.14'::DOUBLE PRECISION;
結果:
“`
double precision

         3.14

“`

例：文字列 '-1000' をより大きな整数型 (BIGINT) に変換する
sql
SELECT '-1000'::BIGINT;
結果:
“`
bigint

-1000
“`

例：文字列 '123.45' を精度指定可能な数値型 (NUMERIC) に変換する
sql
SELECT '123.45'::NUMERIC;
結果:
“`
numeric

123.45
“`

さまざまな数値型に変換可能です。よく使われる数値型には以下のようなものがあります。

  • SMALLINT: 小さな整数（約 -32768 から +32767）
  • INTEGER: 標準的な整数（約 -20億 から +20億）
  • BIGINT: 大きな整数
  • DECIMAL または NUMERIC: 精度を任意に指定できる正確な数値型
  • REAL: 単精度浮動小数点数
  • DOUBLE PRECISION: 倍精度浮動小数点数

注意点:
文字列が対象の数値型として無効な形式である場合、デフォルトではエラーが発生します。
sql
SELECT 'abc'::INTEGER;
結果:
ERROR: invalid input syntax for type integer: "abc"
LINE 1: SELECT 'abc'::INTEGER;
^
また、文字列が数値ではあるものの、対象の数値型の範囲を超える場合もエラー（オーバーフロー）となります。
sql
SELECT '9999999999999999999'::SMALLINT; -- SMALLINTの範囲を超える
結果:
ERROR: integer out of range

1.2 CAST() 関数

CAST() 関数は、標準SQLで定義されている型変換の構文です。PostgreSQL独自の :: 演算子と同様の機能を提供しますが、他のデータベースシステムとの互換性を重視する場合に適しています。

構文:
sql
CAST('文字列' AS 対象の数値型)

例：文字列 '456' を整数型 (INTEGER) に変換する
sql
SELECT CAST('456' AS INTEGER);
結果:
“`
integer

 456

“`

例：文字列 '6.78' を浮動小数点型 (REAL) に変換する
sql
SELECT CAST('6.78' AS REAL);
結果:
“`
real

6.78
“`

:: 演算子と同様に、無効な文字列や範囲外の数値に対してはエラーが発生します。

sql
SELECT CAST('xyz' AS DOUBLE PRECISION);
結果:
ERROR: invalid input syntax for type double precision: "xyz"
LINE 1: SELECT CAST('xyz' AS DOUBLE PRECISION);
^

キャスト演算子 (::) と CAST() 関数の比較:

  • 簡潔さ: :: 演算子のほうが記述が短く済みます。
  • 標準性: CAST() 関数は標準SQLであり、他のデータベースシステムでも一般的にサポートされています。
  • 機能: PostgreSQLにおいては、これらの基本的な変換機能に関して機能的な違いはほとんどありません。どちらを使用しても同じ結果が得られます。

どちらを使用するかは個人の好みやプロジェクトのコーディング規約によりますが、PostgreSQLのユーザーは :: 演算子をよく使用する傾向があります。

2. エラー処理とバリデーション

前述のように、基本的なキャストは変換できない文字列に対してエラーを発生させます。これは意図しないデータが混入していることを検知できるという利点がある一方で、エラーを回避したい場合や、無効なデータは特定のデフォルト値として扱いたい場合には不便です。ここでは、エラーを処理または回避する方法について説明します。

2.1 無効な文字列やNULLの扱い

  • 無効な文字列: 上記の例で見たように、'abc' のような非数値文字を含む文字列を数値型にキャストしようとするとエラーになります。
  • 空文字列 (''): 空文字列を数値型にキャストしようとした場合も、ほとんどの数値型ではエラーになります。
    sql
    SELECT ''::INTEGER;
    結果:
    ERROR: invalid input syntax for type integer: ""
  • NULL値: NULL値を数値型にキャストした場合、結果はNULLになります。これはエラーにはなりません。
    sql
    SELECT NULL::INTEGER;
    結果:
    integer
    ---------
    NULL

データに無効な文字列が含まれている可能性がある場合、単純なキャストだけではクエリ全体が失敗してしまいます。これを避けるためには、事前に文字列をチェックするか、エラーを捕捉する仕組みが必要です。

2.2 エラーを回避する：TRY_CAST (PostgreSQL 13+)

PostgreSQL 13以降では、標準SQLで定義されている TRY_CAST 関数が利用可能です。TRY_CAST は、変換が成功した場合はキャストされた値を返しますが、変換に失敗した場合はエラーを発生させる代わりにNULLを返します。これにより、無効な文字列が含まれていてもクエリが中断されることなく実行を継続できます。

構文:
sql
TRY_CAST('文字列' AS 対象の数値型)

例：有効な文字列と無効な文字列を含む変換
sql
SELECT
TRY_CAST('123' AS INTEGER),
TRY_CAST('abc' AS INTEGER),
TRY_CAST('456' AS DOUBLE PRECISION),
TRY_CAST('xyz' AS DOUBLE PRECISION);
結果:
try_cast | try_cast | try_cast | try_cast
----------+----------+----------+----------
123 | NULL | 456.0 | NULL
(1 row)

TRY_CAST は、クリーンでないデータが含まれる可能性のあるテーブルからデータを抽出・変換する際に非常に役立ちます。NULLになった値を別のデフォルト値に置き換えたい場合は、COALESCE 関数と組み合わせて使用できます。

例：変換できなかった場合に0を返す
sql
SELECT COALESCE(TRY_CAST('abc' AS INTEGER), 0);
結果:
“`
coalesce

    0

“`

例：テーブルカラムに対する TRY_CAST と COALESCE
仮に items テーブルに price_str という TEXT 型のカラムがあり、数値形式の文字列や無効な文字列が含まれているとします。

“`sql
CREATE TABLE items (
item_name TEXT,
price_str TEXT
);

INSERT INTO items (item_name, price_str) VALUES
(‘Apple’, ‘100’),
(‘Banana’, ‘150.50’),
(‘Cherry’, ‘free’), — 無効なデータ
(‘Date’, NULL),
(‘Elderberry’, ”); — 空文字列
“`

これらの文字列を数値として集計したい場合：

sql
SELECT
item_name,
price_str,
TRY_CAST(price_str AS NUMERIC) AS price_numeric,
COALESCE(TRY_CAST(price_str AS NUMERIC), 0) AS price_numeric_default_zero
FROM items;
結果:
item_name | price_str | price_numeric | price_numeric_default_zero
------------+-----------+---------------+----------------------------
Apple | 100 | 100 | 100
Banana | 150.50 | 150.50 | 150.50
Cherry | free | NULL | 0
Date | NULL | NULL | 0
Elderberry | | NULL | 0
(5 rows)
このように、TRY_CAST を使用することで、無効なデータ行があってもクエリが正常に実行され、NULL値として処理できます。

2.3 事前バリデーションと条件付き変換

TRY_CAST が利用できない古いPostgreSQLバージョンを使用している場合や、変換前に文字列が特定のパターンに一致するかどうかを厳密にチェックしたい場合は、正規表現などを用いた事前バリデーションと CASE 文を組み合わせる方法が考えられます。

PostgreSQLでは、正規表現マッチングに ~ 演算子や regexp_match 関数などが使用できます。

例：文字列が非負の整数であるかをチェックする
sql
SELECT '123' ~ '^[0-9]+$'; -- TRUE
SELECT '-123' ~ '^[0-9]+$'; -- FALSE (負の符号を含むため)
SELECT '123a' ~ '^[0-9]+$'; -- FALSE (非数字を含むため)
SELECT '' ~ '^[0-9]+$'; -- FALSE (空文字列)

例：文字列が符号付き整数であるかをチェックする
sql
SELECT '123' ~ '^[+-]?[0-9]+$'; -- TRUE
SELECT '-123' ~ '^[+-]?[0-9]+$'; -- TRUE
SELECT '+123' ~ '^[+-]?[0-9]+$'; -- TRUE
SELECT 'abc' ~ '^[+-]?[0-9]+$'; -- FALSE

例：文字列が数値（整数または小数点数、指数表記も含む場合がある）であるかをチェックする正規表現は複雑になりがちです。単純な数値形式のチェックには ^[-+]?[0-9]*(\.[0-9]+)?([eE][-+]?[0-9]+)?$ のようなパターンが考えられますが、完全性は保証しにくいです。

これらの正規表現チェックと CASE 文を組み合わせることで、変換可能な文字列のみを変換し、それ以外はNULLや他の値として扱うことができます。

例：正規表現でチェックして変換する
sql
SELECT
price_str,
CASE
WHEN price_str ~ '^[0-9]+$' THEN price_str::INTEGER -- 非負の整数のみ変換
ELSE NULL
END AS price_converted
FROM items;
結果:
price_str | price_converted
-----------+-----------------
100 | 100
150.50 | NULL -- 小数を含むためパターンに一致しない
free | NULL -- 非数字を含むため
NULL | NULL -- NULLはパターンマッチングの結果もNULL
| NULL -- 空文字列はパターンに一致しない
(5 rows)
この方法の欠点は、正規表現が複雑になると記述が難しくなることと、TRY_CAST と比較して冗長になることです。しかし、特定の書式のみを許可したい場合には有効な手段となります。

3. 特定の数値型への変換の詳細

文字列をどの数値型に変換するかによって、結果や注意点が異なります。ここでは、いくつかの主要な数値型への変換における詳細を説明します。

3.1 整数型 (SMALLINT, INTEGER, BIGINT)

文字列を整数型に変換する場合、文字列は整数形式である必要があります。小数点以下の部分を持つ文字列を整数型に直接キャストしようとすると、通常はエラーになります。

sql
SELECT '123.45'::INTEGER;
結果:
ERROR: invalid input syntax for type integer: "123.45"

小数点以下を切り捨てたり丸めたりして整数に変換したい場合は、一度浮動小数点型や NUMERIC 型に変換してから、別途提供されている数学関数を使用する必要があります。

例：小数点以下を切り捨てる (TRUNC 関数)
sql
SELECT TRUNC('123.45'::NUMERIC); -- NUMERICに変換してからTRUNC
結果:
“`
trunc

123
“`

例：四捨五入する (ROUND 関数)
sql
SELECT ROUND('123.55'::NUMERIC); -- NUMERICに変換してからROUND
結果:
“`
round

124
“`

例：小数点以下を常に切り下げる (FLOOR 関数) / 切り上げる (CEIL 関数)
sql
SELECT FLOOR('123.99'::NUMERIC);
SELECT CEIL('123.01'::NUMERIC);
結果:
“`
floor

123
(1 row)

ceil

124
(1 row)
``
これらの数学関数はNUMERIC型や浮動小数点型を引数に取ります。したがって、文字列を直接これらの関数の引数にすることはできません。まず文字列を適切な数値型（NUMERIC,REAL,DOUBLE PRECISION`）にキャストしてから関数を適用する必要があります。

また、変換対象の数値型の範囲にも注意が必要です。例えば、'40000' を SMALLINT にキャストしようとすると範囲オーバーでエラーになりますが、INTEGER や BIGINT にキャストすれば成功します。

sql
SELECT '40000'::SMALLINT; -- ERROR
SELECT '40000'::INTEGER; -- OK

3.2 浮動小数点型 (REAL, DOUBLE PRECISION)

文字列を浮動小数点型に変換する場合、文字列は整数部分、小数点、小数点以下の部分、および必要に応じて指数表記を含むことができます。

例：小数点を含む文字列
sql
SELECT '123.45'::REAL;
SELECT '-98.7'::DOUBLE PRECISION;
結果:
“`
real

123.45
(1 row)

double precision

      -98.7

(1 row)
“`

例：指数表記を含む文字列
sql
SELECT '1.23e4'::DOUBLE PRECISION; -- 1.23 * 10^4 = 12300
SELECT '-5.67E-2'::REAL; -- -5.67 * 10^-2 = -0.0567
結果:
“`
double precision

        12300

(1 row)

real

-0.0567
(1 row)
“`

浮動小数点型は、NUMERIC 型と比較して精度に限界があります。非常に大きな数値や小数点以下が非常に多い数値を扱う場合、丸め誤差が発生する可能性があります。正確な計算（特に金融関連など）が必要な場合は、後述する NUMERIC 型を使用することを強く推奨します。

3.3 精度指定可能な数値型 (NUMERIC, DECIMAL)

NUMERIC 型（DECIMAL はそのエイリアス）は、任意精度をサポートする数値型です。桁数が多い数値や、小数点以下を正確に表現する必要がある場合に最適です。型定義時に精度（p: 全体の桁数）とスケール（s: 小数点以下の桁数）を指定できます。

構文: NUMERIC(p, s) または DECIMAL(p, s)

  • p (precision): 数値全体の桁数（小数点を含まない）。省略可能で、省略した場合はシステムが決定可能な精度（通常は非常に高い）になります。
  • s (scale): 小数点以下の桁数。省略可能で、省略した場合は0とみなされます（つまり整数部のみ）。p を省略しない場合は s も省略できません。

例：精度とスケールを指定して変換
sql
SELECT '123.4567'::NUMERIC(8, 4); -- 全体8桁、小数点以下4桁
結果:
“`
numeric

123.4567
“`

変換元の文字列が指定された精度を超える場合、PostgreSQLはデフォルトではエラーを発生させます。

例：スケールを超える場合のエラー
sql
SELECT '123.456'::NUMERIC(5, 2); -- スケールが2なのに文字列は小数点以下3桁
結果:
ERROR: 22003: numeric field overflow
DETAIL: A field with precision 5, scale 2 must round to an absolute value less than 10^3.
このエラーメッセージは、指定された NUMERIC(5, 2) は最大で 999.99 までしか表現できないことを示唆しています（全体5桁、小数点以下2桁）。

ただし、変換元の文字列の精度やスケールが指定された精度やスケールよりも小さい場合は、問題なく変換できます。必要に応じてゼロで埋められたり、小数点以下の桁数が減らされたりします（この場合、丸めではなく切り捨てられます）。

例：指定されたスケールよりも文字列の小数点以下が少ない場合
sql
SELECT '123.4'::NUMERIC(5, 2);
結果:
“`
numeric

123.40
“`

例：精度を省略した場合
sql
SELECT '1234567890.1234567890'::NUMERIC; -- 高い精度で格納される
結果:
“`
numeric

1234567890.1234567890
“`

NUMERIC 型は正確な計算が必要な場合に非常に有用ですが、浮動小数点型と比較して計算処理に時間がかかる場合があります。

4. ロケールや書式を考慮した変換：to_number 関数

これまでに見てきた基本的なキャスト方法 (::, CAST, TRY_CAST) は、小数点としてピリオド (.)、桁区切りとしてカンマ (,) を含まない（またはカンマを無視しない）比較的シンプルな数値形式の文字列には有効です。しかし、データベースのロケール設定や、文字列が通貨記号 ($, € など)、桁区切り文字（多くの国でカンマを使用）、または小数点文字（多くのヨーロッパ諸国でカンマを使用）などを含む複雑な書式を持っている場合、これらの基本的なキャストでは正しく変換できません。

PostgreSQLは、このような複雑な書式を持つ文字列を数値に変換するために to_number 関数を提供しています。この関数は、変換対象の文字列と、その書式を指定するテンプレート文字列を引数として受け取ります。

構文:
sql
to_number(text, text)
* 第一引数: 変換したい文字列。
* 第二引数: 文字列の書式を指定するテンプレート。

書式テンプレートは、文字列中の各要素（数字、小数点、桁区切り、通貨記号など）が何を表しているかを指定します。主要な書式テンプレート要素を以下に示します。

  • 9: 数値を表します。先行ゼロは表示されません。
  • 0: 数値を表します。必要に応じて先行ゼロが表示されます。
  • . (ピリオド): 小数点の位置を示します。実際の小数点文字はロケールによって異なります。
  • , (カンマ): 桁区切りの位置を示します。実際の桁区切り文字はロケールによって異なります。
  • FM: “Fill Mode” を有効にします。先行/末尾の空白やゼロを取り除きます（to_char でよく使われますが、to_number では空白などを無視するのに役立ちます）。
  • L: 通貨記号（Local currency symbol）。ロケールによって決まります。
  • PR: マイナス値を山括弧 <> で囲んで表示/認識します。
  • S: 符号 (+ または -)。文字列の先頭または末尾に指定します。
  • MI: マイナス記号 (-) を文字列の末尾に指定します。
  • PL: プラス記号 (+) を文字列の末尾に指定します。
  • SG: プラスまたはマイナス記号を文字列の先頭または末尾に指定します。
  • TH: 序数接尾辞（例えば “1st”, “2nd” の “st”, “nd”）。to_char で使われますが、to_number では無視されます。
  • V: 指定された桁数だけ数値を10の冪乗で乗算します（入力文字列には小数点がないとみなされます）。例えば 99V99 で '1234' を変換すると 12.34 になります。
  • EEEE: 指数表記（例: ‘1.23e+04’）。

to_number 関数は、指定された書式テンプレートに従って文字列を解析し、NUMERIC 型の値を返します。

例：桁区切り文字を含む文字列の変換
sql
SELECT to_number('1,234,567', '9G999G999'); -- Gはロケール依存の桁区切り文字
デフォルトのロケール（通常 C または US）では . が小数点、, が桁区切りなので、これでうまくいきます。
結果:
“`
to_number

1234567
“`

例：小数点と桁区切り文字を含む文字列の変換
sql
SELECT to_number('1,234.56', '9G999D99'); -- Gは桁区切り, Dはロケール依存の小数点文字
結果:
“`
to_number

1234.56
“`

例：通貨記号を含む文字列の変換
sql
SELECT to_number('$1,234.56', 'L9G999D99'); -- Lはロケール依存の通貨記号
結果:
“`
to_number

1234.56
``
PostgreSQLのロケール設定（LC_MONETARY`）が通貨記号の解釈に影響します。

例：マイナス値の異なる表記の変換
sql
SELECT to_number('-100', 'S999'); -- Sは符号
SELECT to_number('100-', '999MI'); -- MIは末尾のマイナス
SELECT to_number('<100>', 'PR999'); -- PRは山括弧
結果:
“`
to_number

  -100

(1 row)

to_number

  -100

(1 row)

to_number

  -100

(1 row)
“`

例：指数表記を含む文字列の変換
sql
SELECT to_number('1.23e+04', '9D99EEEE'); -- EEEEは指数表記
結果:
“`
to_number

 12300

“`

to_number 関数も、指定された書式と文字列が一致しない場合や、数値として認識できない文字が含まれている場合はエラーを発生させます。

sql
SELECT to_number('abc', '999'); -- 無効な文字
結果:
ERROR: invalid input syntax for type numeric: "abc"
sql
SELECT to_number('123', '999G999'); -- 書式と一致しない
結果:
ERROR: more than one data left in input string

to_number 関数でエラーを回避したい場合は、PostgreSQL 13以降であれば TRY_CAST(text AS numeric, text) 形式の TRY_CAST はありませんが、一旦 TRY_CAST(text AS text) で元の文字列をTRY_CASTしてから to_number を呼び出すことはできません。to_number 自体にはエラー無視の機能はありません。したがって、to_number を安全に使用するには、事前に文字列のバリデーションを行うか、エラーが発生することを許容するか、あるいは PL/pgSQL などの手続き言語で例外処理を行う必要があります。

to_number とロケール:

to_number 関数は、PostgreSQLサーバーのロケール設定、特に LC_NUMERIC および LC_MONETARY の設定に影響を受けます。これらの設定によって、小数点文字 (D)、桁区切り文字 (G)、通貨記号 (L) として認識される文字が変わります。
例えば、ヨーロッパのロケール（例: fr_FR）では、小数点にカンマ (,)、桁区切りにピリオド (.) が使用されるのが一般的です。

sql
-- サーバーのロケール設定を確認 (例: SET LC_NUMERIC TO 'fr_FR'; が実行されている場合)
SELECT to_number('1.234,56', '9G999D99'); -- ロケールがfr_FRなら小数点として','、桁区切りとして'.'を期待
ロケール設定が期待通りの文字になっていない場合、to_number は正しく動作しないかエラーになります。ロケール設定に依存しない変換を行いたい場合は、文字列中の特定文字を REPLACE 関数などで置換してから基本的なキャストを行うか、ロケールを一時的に変更してから to_number を実行するなどの工夫が必要になります。

5. その他の考慮事項と応用

5.1 前後の空白文字

変換対象の文字列に前後の空白文字が含まれている場合、PostgreSQLの基本的なキャストは通常これを無視してくれます。

sql
SELECT ' 123 '::INTEGER;
SELECT ' 456.78 '::NUMERIC;
結果:
“`
integer

 123

(1 row)

numeric

456.78
(1 row)
“`

しかし、文字列の途中に空白が含まれている場合はエラーになります。
sql
SELECT '1 2 3'::INTEGER;
結果:
ERROR: invalid input syntax for type integer: "1 2 3"
to_number 関数も通常、前後の空白を無視しますが、書式指定によっては影響を受ける可能性もあります。文字列のクリーンアップを徹底したい場合は、変換前に TRIM() 関数を使用して前後の空白を除去するのが安全です。

sql
SELECT TRIM(' 123 ')::INTEGER;
結果:
“`
integer

 123

“`

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

  • インデックスの利用: 文字列型カラムに格納された数値を条件としてデータを検索する場合、単純なwhere句で型変換を行うと、そのカラムに作成されたインデックスが利用されないことがあります。

    sql
    -- string_value カラムが TEXT 型でインデックスが作成されているとする
    SELECT * FROM my_table WHERE string_value::INTEGER > 100;
    このクエリでは、string_value::INTEGER は式として評価されるため、string_value カラム自体のインデックスは通常使用されません。代わりに、テーブル全体をスキャンして各行で型変換を行い、条件を満たすかチェックするという非効率な処理になります。

    これを改善するには、以下の方法があります。
    * 関数インデックス: 型変換を含む式に対するインデックスを作成します。
    sql
    CREATE INDEX idx_my_table_string_value_int ON my_table ((string_value::INTEGER));
    これにより、WHERE string_value::INTEGER > 100 のようなクエリでこのインデックスが利用されるようになります。ただし、無効な文字列が含まれているとインデックス作成時にエラーが発生する可能性があるため、注意が必要です。また、TRY_CAST に対しては関数インデックスを作成できません。

    • 生成列 (Generated Columns, PostgreSQL 12+): PostgreSQL 12以降では、他の列の値から計算される「生成列」を定義できます。文字列カラムから数値型の生成列を作成し、その生成列にインデックスを作成することで、効率的な検索が可能です。
      “`sql
      — 無効なデータがある可能性を考慮して TRY_CAST を使用 (PostgreSQL 13+)
      ALTER TABLE my_table ADD COLUMN numeric_value INTEGER GENERATED ALWAYS AS (TRY_CAST(string_value AS INTEGER)) STORED;
      — または、無効なデータがないことが分かっている場合やエラーを許容する場合
      — ALTER TABLE my_table ADD COLUMN numeric_value INTEGER GENERATED ALWAYS AS (string_value::INTEGER) STORED;

      CREATE INDEX idx_my_table_numeric_value ON my_table (numeric_value);

      — これにより、以下のクエリが生成列のインデックスを利用できるようになる
      SELECT * FROM my_table WHERE numeric_value > 100;
      “`
      生成列はストレージを消費しますが、クエリ実行時の計算コストを削減し、インデックス利用による検索性能向上に大きく貢献します。

  • 変換処理自体のコスト: 大量のデータを変換する場合、変換処理自体にもCPUコストがかかります。:: や CAST は比較的軽量ですが、to_number は書式解析が必要なため、より多くのコストがかかる可能性があります。パフォーマンスが重要な場合は、適切な変換方法の選択と、必要に応じたインデックス戦略が不可欠です。

5.3 応用例

  • 集計: 文字列カラムを数値に変換して集計関数（SUM, AVG, MAX, MIN など）を適用する。
    sql
    -- price_str カラムを集計 (エラー無視のため TRY_CAST と COALESCE を使用)
    SELECT SUM(COALESCE(TRY_CAST(price_str AS NUMERIC), 0)) AS total_price
    FROM items;

  • JOIN 条件: 異なるテーブル間で、片方のテーブルの文字列カラムともう一方のテーブルの数値カラムをJOINする。
    “`sql
    — products.product_id が INTEGER, orders.product_id_str が TEXT
    SELECT p.product_name, COUNT(o.order_id)
    FROM products p
    JOIN orders o ON p.product_id = o.product_id_str::INTEGER — 注意: インデックスが効きにくい
    GROUP BY p.product_name;

    — 改善策として、orders テーブルに数値型の生成列を追加するか、JOIN前にサブクエリで変換するなど考慮が必要。
    “`

  • データクレンジング/移行: 文字列型のデータを数値型を持つ新しいテーブルに移行する際に変換する。
    sql
    INSERT INTO new_items (item_name, price_numeric)
    SELECT item_name, COALESCE(TRY_CAST(price_str AS NUMERIC), 0)
    FROM items;

6. よくある落とし穴とトラブルシューティング

  • 非数値文字の混入: 数値形式の文字列に非数字（例: '123a', 'free', 'N/A'）が含まれている場合、基本的なキャストや to_number はエラーになります。TRY_CAST を使うか、正規表現で事前チェックするか、REPLACE で不要文字を除去するなどの対応が必要です。
    sql
    -- 例: 通貨記号と桁区切り、そして末尾に '円' が付いている場合
    SELECT REPLACE(REPLACE('¥1,234円', '¥', ''), '円', '')::NUMERIC; -- まず記号を除去してからキャスト
    -- 期待する結果にならない場合や、複雑な場合は to_number を検討
    SELECT to_number('¥1,234', 'L9G999'); -- '円' は to_number の書式で扱えないためREPLACEが必要

  • 空文字列 ('') の扱い: 空文字列はほとんどの数値型にキャストするとエラーになります。TRY_CAST は NULL を返します。空文字列を NULL または 0 として扱いたい場合は、TRY_CAST や CASE WHEN string_col = '' THEN NULL ELSE string_col::numeric END のようなロジックが必要です。

  • NULL値: NULL値は数値型にキャストしてもエラーにならず、NULLのままです。NULLを0などのデフォルト値にしたい場合は COALESCE を使用します。

  • ロケールによる違い: to_number 関数を使う場合、小数点や桁区切り文字がロケールに依存することを理解しておく必要があります。データベースまたはセッションの LC_NUMERIC および LC_MONETARY 設定を確認してください。ロケールに依存しないようにするには、REPLACE で特定の文字を置換してからキャストするか、書式テンプレートをハードコーディングする際に文字そのもの (. や ,) を使う代わりに D や G といったロケール依存の書式要素を使うようにします。

  • 型の範囲オーバーフロー: 変換後の数値が対象の数値型の範囲を超える場合、エラーが発生します。格納したい数値の最大値や最小値を考慮して、適切な数値型（特に BIGINT や NUMERIC）を選択することが重要です。

  • 浮動小数点数の精度: REAL や DOUBLE PRECISION はバイナリ浮動小数点表現を使用するため、計算によっては丸め誤差が発生する可能性があります。正確な計算が必要な場合は必ず NUMERIC 型を使用してください。

  • 日付/時刻文字列との混同: まれに、数値と見分けにくい日付/時刻形式の文字列（例: '20230101'）を数値に変換したい場合があります。このような場合は、意図した数値に変換できるかを事前に十分テストする必要があります。

7. まとめ

PostgreSQLで文字列を数値に変換する方法は複数あり、それぞれ異なるシナリオに適しています。

  • :: 演算子 / CAST() 関数: 最も基本的で簡潔な方法です。クリーンなデータや、変換できない場合にエラーを発生させて問題に気づきたい場合に適しています。小数点、桁区切り、通貨記号など、複雑な書式には対応できません。

  • TRY_CAST() (PostgreSQL 13+): 変換できない場合にNULLを返すため、エラーを回避し、クエリの継続性を確保したい場合に非常に便利です。無効なデータが含まれる可能性のある外部データやユーザー入力を扱う場合に特に有効です。COALESCE と組み合わせてデフォルト値を設定することも簡単です。複雑な書式には対応できません。

  • to_number(text, text): 桁区切り、小数点、通貨記号など、ロケールや特定の書式を含む複雑な文字列を数値 (NUMERIC) に変換する場合に強力です。書式テンプレートを正しく指定する必要があります。変換できない場合はエラーになります（TRY_CAST のようなエラー回避機能は直接はありません）。

  • 事前バリデーション + CASE 文: TRY_CAST が使えない古いバージョンや、特定のパターンに厳密に一致する場合のみ変換したい場合に、正規表現などと組み合わせて使用します。記述が複雑になりがちです。

変換方法を選択する際は、以下の点を考慮してください。

  1. 文字列の形式: シンプルな数値形式か、複雑な書式（桁区切り、小数点、通貨記号など）を含むか。
  2. エラー発生時の挙動: 変換できない場合にエラーで処理を止めたいか、それともNULLやデフォルト値として処理を続けたいか。
  3. PostgreSQLのバージョン: TRY_CAST はバージョン13以降でのみ利用可能です。
  4. 必要な数値型: 整数、浮動小数点、精度指定が必要な数値など、目的の計算に適した型を選択します。小数点以下の扱い（切り捨て、四捨五入など）が必要な場合は、追加の数学関数を組み合わせます。
  5. パフォーマンス: 特に大規模なテーブルで変換や変換結果での検索を行う場合、インデックス戦略（関数インデックス、生成列）や変換処理のコストを考慮する必要があります。

PostgreSQLは文字列から数値への変換に関して柔軟で強力なツールを提供しています。これらの方法を理解し、状況に応じて適切な手法を選択することで、データの整合性を保ちつつ、効率的にデータベースを操作することができます。

この記事が、PostgreSQLにおける文字列から数値への変換に関する理解を深め、日々のデータベース操作や開発の一助となれば幸いです。

コメントする

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

上部へスクロール