PostgreSQLでゼロ割り算エラー対策

SQLで0で割ったときに出るエラーの、22012(DIVISION_BY_ZERO)を楽に防ぐ方法。

完全に防ぐことはできないので、安全に割り算ができる特殊な演算子をCREATE OPERATORで作成して、ゼロ割り算になる可能性のある部分に適用するようにする。

まずは以下のSQLを実行する。

create function st_zero_div_to_null(numeric, numeric) returns numeric as '
select case $2 when 0 then null else $1 / $2 end;
' language sql;

create function st_zero_div_to_null(int, numeric) returns numeric as '
select case $2 when 0 then null else $1 / $2 end;
' language sql;

create function st_zero_div_to_null(numeric, int) returns numeric as '
select case $2 when 0 then null else $1 / $2 end;
' language sql;

create function st_zero_div_to_null(int, int) returns int as '
select case $2 when 0 then null else $1 / $2 end;
' language sql;

create operator // (
procedure = st_zero_div_to_null,
leftarg = int,
rightarg = int
);

create operator // (
procedure = st_zero_div_to_null,
leftarg = int,
rightarg = numeric
);

create operator // (
procedure = st_zero_div_to_null,
leftarg = numeric,
rightarg = int
);

create operator // (
procedure = st_zero_div_to_null,
leftarg = numeric,
rightarg = numeric
);

ゼロで割る可能性のあるSQLで、割り算の演算子[/]の代わりに[//]を使うと、ゼロ割り算の時にNULLを返すことができる。

select 5 / 0  --error
select 5 // 0 --NULL
select 6 / 3  -- 2
select 6 // 3 -- 2