mysql中除数为0_PostgreSQL被除数为0处理方法

0不能作为被除数这是数学中的常识，当在数据库中除以0时，应该如何处理呢？

在oracle和pg中是不允许被除数为0的。

oracle：https://www.cndba.cn/foucus/article/4093

https://www.cndba.cn/foucus/article/4093

SQL> select 1/0 from dual;

select 1/0 from dual

*

ERROR at line 1:

ORA-01476: divisor is equal to zero

PostgreSQL：

bill@bill=>/set VERBOSITY verbose

bill@bill=>select 1/0;

ERROR: 22012: division by zero

LOCATION: int4div, int.c:824

我们可以在pg的代码中看到该报错的解释：https://www.cndba.cn/foucus/article/4093https://www.cndba.cn/foucus/article/4093

813 Datum

814 int4div(PG_FUNCTION_ARGS)

815 {

816 int32 arg1 = PG_GETARG_INT32(0);

817 int32 arg2 = PG_GETARG_INT32(1);

818 int32 result;

819

820 if (arg2 == 0)

821 {

822 ereport(ERROR,

823 (errcode(ERRCODE_DIVISION_BY_ZERO),

824 errmsg("division by zero")));

825 /* ensure compiler realizes we mustn't reach the division (gcc bug) */

826 PG_RETURN_NULL();

827 }

但是，如果业务要求当被除数=0时，像mysql中一样返回空，而不是报错，在pg中该如何实现呢？

mysql：https://www.cndba.cn/foucus/article/4093https://www.cndba.cn/foucus/article/4093https://www.cndba.cn/foucus/article/4093

mysql> select 1/0;

+------+

| 1/0 |

+------+

| NULL |

+------+

1 row in set, 1 warning (0.03 sec)

pg中对应的处理方法：

https://www.cndba.cn/foucus/article/4093

bill@bill=>select case when c2=0 then null else c1/c2 end from t2;

case

------

2

1

(3 rows)

—方法二：

自定义操作符https://www.cndba.cn/foucus/article/4093

bill@bill=>create or replace function div_zero(numeric, numeric) returns numeric as $$

bill$# select case when $2 <> 0 then $1/$2 else null end ;

bill$# $$ language sql strict immutable;

CREATE FUNCTION

bill@bill=>create operator // (procedure=div_zero, leftarg=numeric, rightarg=numeric);

CREATE OPERATOR

bill@bill=>select 1//0;

?column?

----------

(1 row)

版权声明：本文为博主原创文章，未经博主允许不得转载。