IS [NOT] INFINITE

SELECTWHERE子句中使用,判断输入数值是否为Infinity。若为非数字,则会转换为数字,若转换失败,则报错。

存在以下特殊情形:

  • NULL:对于输入为NULL,返回NULL
  • 字符串Infinity(忽略大小写):可以转换为数值Infinity,但在表达式中判断为非Infinity,报错。

IS INFINITE

如果输入数值为Infinity,则返回true,否则返回false。对于输入NULL,返回NULL

示例:

openGauss=# create table t_infinitetest (c_id int, c_number float4, c_txt text);
CREATE TABLE
openGauss=# insert into t_infinitetest values (1, 3.14, '3.14');
INSERT 0 1
openGauss=# insert into t_infinitetest values (2, 'Infinity'::float4, 'FM95.28');
INSERT 0 1
openGauss=# insert into t_infinitetest values (3, NULL, 'Infinity');
INSERT 0 1
openGauss=# select c_number is infinite from t_infinitetest order by c_id;
 ?column?
----------
 f
 t

(3 rows)

openGauss=# select * from t_infinitetest where c_id = 1 and c_txt is infinite;
 c_id | c_number | c_txt
------+----------+-------
(0 rows)

openGauss=# select * from t_infinitetest where c_id = 2 and c_txt is infinite;
ERROR:  invalid input syntax for type double precision: "FM95.28"
openGauss=# select * from t_infinitetest where c_id = 3 and c_txt is infinite;
ERROR:  invalid input for IS [NOT] INFINITE

IS NOT INFINITE

如果输入数值为Infinity,则返回false,否则返回true。对于输入NULL,返回NULL

示例:

openGauss=# select c_number is not infinite from t_infinitetest order by c_id;
 ?column?
----------
 t
 f

(3 rows)

openGauss=# select * from t_infinitetest where c_id = 1 and c_txt is not infinite;
 c_id | c_number | c_txt
------+----------+-------
    1 |     3.14 | 3.14
(1 row)

openGauss=# select * from t_infinitetest where c_id = 2 and c_txt is not infinite;
ERROR:  invalid input syntax for type double precision: "FM95.28"
openGauss=# select * from t_infinitetest where c_id = 3 and c_txt is not infinite;
ERROR:  invalid input for IS [NOT] INFINITE
意见反馈
编组 3备份
    openGauss 2025-02-15 23:22:43
    取消