[解決済み] PostgreSQL での isnumeric()

質問

SQL文の中で、与えられた文字列が数値（整数または浮動小数点）として解釈されるかどうかを判断する必要があります。以下のように。

SELECT AVG(CASE WHEN x ~ '^[0-9]*.?[0-9]*$' THEN x::float ELSE NULL END) FROM test

私は、Postgresの パターンマッチング が使えそうです。そこで ここ を使い、浮動小数点数を取り入れた。これが私のコードです。

WITH test(x) AS (
    VALUES (''), ('.'), ('.0'), ('0.'), ('0'), ('1'), ('123'),
    ('123.456'), ('abc'), ('1..2'), ('1.2.3.4'))

SELECT x
     , x ~ '^[0-9]*.?[0-9]*$' AS isnumeric
FROM test;

出力されます。

    x    | isnumeric 
---------+-----------
         | t
 .       | t
 .0      | t
 0.      | t
 0       | t
 1       | t
 123     | t
 123.456 | t
 abc     | f
 1..2    | f
 1.2.3.4 | f
(11 rows)

ご覧の通り、最初の2項目（空文字列の '' と唯一のピリオドである '.' ) が数値型であると誤判定されます (実際はそうではありません)。今のところこれ以上近づけません。何か助言をお願いします。

更新情報 ベースは この回答 (とそのコメント）にパターンを合わせました。

WITH test(x) AS (
    VALUES (''), ('.'), ('.0'), ('0.'), ('0'), ('1'), ('123'),
    ('123.456'), ('abc'), ('1..2'), ('1.2.3.4'), ('1x234'), ('1.234e-5'))

SELECT x
     , x ~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$' AS isnumeric
FROM test;

どちらが与えるか。

     x    | isnumeric 
----------+-----------
          | f
 .        | f
 .0       | t
 0.       | t
 0        | t
 1        | t
 123      | t
 123.456  | t
 abc      | f
 1..2     | f
 1.2.3.4  | f
 1x234    | f
 1.234e-5 | f
(13 rows)

科学的記数法や負の数の扱いに、今見るとまだ問題がありますね。

どのように解決するのですか？

お気づきのように、正規表現に基づく方法は、正しく行うことがほとんど不可能です。例えば、あなたのテストでは 1.234e-5 は有効な数字ではありませんが、実際には有効です。また、負の数も見逃しています。数字のように見えるものでも、それを格納しようとするとオーバーフローを起こすとしたらどうでしょう？

に実際にキャストしようとする関数を作成することをお勧めします。 NUMERIC (または FLOAT を返し、タスクがそれを必要とする場合は TRUE または FALSE このキャストが成功したかどうかによって異なります。

このコードで関数 ISNUMERIC() :

CREATE OR REPLACE FUNCTION isnumeric(text) RETURNS BOOLEAN AS $$
DECLARE x NUMERIC;
BEGIN
    x = $1::NUMERIC;
    RETURN TRUE;
EXCEPTION WHEN others THEN
    RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE;

この関数をデータに対して呼び出すと、次のような結果が得られます。

WITH test(x) AS ( VALUES (''), ('.'), ('.0'), ('0.'), ('0'), ('1'), ('123'),
  ('123.456'), ('abc'), ('1..2'), ('1.2.3.4'), ('1x234'), ('1.234e-5'))
SELECT x, isnumeric(x) FROM test;

    x     | isnumeric
----------+-----------
          | f
 .        | f
 .0       | t
 0.       | t
 0        | t
 1        | t
 123      | t
 123.456  | t
 abc      | f
 1..2     | f
 1.2.3.4  | f
 1x234    | f
 1.234e-5 | t
 (13 rows)

より正しく、読みやすいだけでなく、データが実際に数字であった場合、より速く動作します。