Isnumeric() с PostgreSQL

Мне нужно определить, может ли данная строка интерпретироваться как число (целое число или плавающая точка) в инструкции 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)

Как вы можете видеть, первые два элемента (пустая строка '' и единственный период '.') ошибочно классифицируются как числовой тип (которых они не являются). На данный момент я не могу приблизиться к этому. Любая помощь ценится!

Обновить На основе этого ответа (и его комментариев) я адаптировал шаблон для:

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

Как вы можете заметить, метод, основанный на регулярном выражении, почти невозможно сделать правильно. Например, в вашем тесте указано, что 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)

Не только это более корректно и легче читать, но и будет работать быстрее, если на самом деле это число.

Ответ 2

Задача - это два 0 или более элемента [0-9] на каждой стороне десятичной точки. Вам нужно использовать логический OR | в строке идентификации номера:

~'^([0-9]+\.?[0-9]*|\.[0-9]+)$'

Это будет исключать только десятичную точку в качестве допустимого числа.