postgre 判断是否为数字

1.创建函数

CREATE OR REPLACE FUNCTION isnumeric(txtStr VARCHAR) RETURNS BOOLEAN AS $$ BEGIN RETURN txtStr ~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$';END;$$LANGUAGE'plpgsql';

2.查询样例

select * from nk_template_20220727155157 where isnumeric(字段) =true

select sum(case when isnumeric(字段) =true then string1 else 0 end),sum(case when isnumeric(字段) =true then 0 else 1 end) from nk_template_20220727155157