PostgreSQL 在被除数=0时的小纯真和小倔强

less than 1 minute read

背景

0不能作为被除数,小学就学过的知识。

对于数据库来说,设计严谨,遵循一些基本的原则也是很有必要的。

当在数据库中除以0时,应该如何处理呢?

PostgreSQL为例,它具有非常浓烈的学院派风格,你说它你能让你除以0吗?

显然不让,如下:

postgres=# select 1/0;  
ERROR:  22012: division by zero  
LOCATION:  int4div, int.c:719  

代码中,我们可以看到对应的报错

Datum  
int4div(PG_FUNCTION_ARGS)  
{  
        int32           arg1 = PG_GETARG_INT32(0);  
        int32           arg2 = PG_GETARG_INT32(1);  
        int32           result;  
  
        if (arg2 == 0)  
        {  
                ereport(ERROR,  
                                (errcode(ERRCODE_DIVISION_BY_ZERO),  
                                 errmsg("division by zero")));  
                /* ensure compiler realizes we mustn't reach the division (gcc bug) */  
                PG_RETURN_NULL();  
        }  

但是,如果业务要求当被除数=0时,返回空,而不是报错,(又或者返回其他值)应该怎么处理呢?

除以0时返回空如何处理?

方法1

SQL标准中,有case的语法,可以用来支持被除数=0时返回其他值。

写法举例

select case when c2=0 then null else c1/c2 end from tbl ....;  

方法2

自定义操作符

PostgreSQL允许用户自定义操作符,实现业务需求的逻辑

例子

create or replace function div_zero(numeric, numeric) returns numeric as $$                 
select case when $2 <> 0 then $1/$2 else null end ;                
$$ language sql strict immutable;  
  
postgres=# create operator // (procedure=div_zero, leftarg=numeric, rightarg=numeric);  
CREATE OPERATOR  
postgres=# select 1//0;  
 ?column?   
----------  
           
(1 row)  
  
postgres=# select 1//1.1;  
        ?column?          
------------------------  
 0.90909090909090909091  
(1 row)  

方法3

修改PG源码,新增一个GUC变量如enable_divs_zero,默认为off,则使用原有的处理方式。

当enable_divs_zero=on时,返回NULL.

例如对于INT类型,修改这个,其他类型修改对应的.c即可

Datum  
int4div(PG_FUNCTION_ARGS)  
{  
        int32           arg1 = PG_GETARG_INT32(0);  
        int32           arg2 = PG_GETARG_INT32(1);  
        int32           result;  
  
        if (arg2 == 0)  
        {  
                // 加GUC判断,如果enable_divs_zero=off, 报错, 否则返回NULL  
		ereport(ERROR,  
                                (errcode(ERRCODE_DIVISION_BY_ZERO),  
                                 errmsg("division by zero")));  
                  
                /* ensure compiler realizes we mustn't reach the division (gcc bug) */  
                PG_RETURN_NULL();  
        }  

小结

PostgreSQL在很多方面都有一些学院派的小倔强,或者说趋于严谨的作风。

不过它很多开放的接口,所以,用户有什么特殊的需求,都是可以解决的。

Flag Counter

digoal’s 大量PostgreSQL文章入口