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

标签

PostgreSQL , 被除数=0 , UDF , 自定义操作符

背景

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在很多方面都有一些学院派的小倔强，或者说趋于严谨的作风。

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