PostgreSQL 在被除数=0时的小纯真和小倔强
背景
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在很多方面都有一些学院派的小倔强,或者说趋于严谨的作风。
不过它很多开放的接口,所以,用户有什么特殊的需求,都是可以解决的。