我需要确定一个给定的字符串是否可以解释为sql语句中的数字(整数或浮点数)。如下：
SELECT AVG(CASE WHEN x ~ '^[0-9]*.?[0-9]*$' THEN x::float ELSE NULL END) FROM test
我发现Postgres的pattern matching可以用于此。因此，我调整了this place年提出的声明，以纳入浮点数。这是我的代码：
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)
正如你所看到的，前两个项目(空字符串“和唯一周期”)被错误地分类为数字类型(它们不是)。我现在不能再接近这个了。任何帮助赞赏！
更新基于this answer(及其注释)，我改编了以下模式：
WITH test(x) AS (
VALUES (''),('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，具体取决于此转换是否成功。
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 (''),('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)
它不仅更正确和更容易阅读，如果数据实际上是一个数字，它也会更快地工作。