PostgreSQL使用NULLIF仍触发division by zero除零错误如何解决

问题原因 #

你只对除法运算的分子oil做了0值处理，没有处理分母hourly_rate的0值，这是触发除零报错的核心原因。

你计算hourly_rate的逻辑是取同一油井相邻两条生产记录的时间差换算为小时数，当出现以下两种情况时会导致hourly_rate异常：

• 同一油井的两条连续记录produced_at值完全相同，计算出的时间差为0，最终得到的hourly_rate为0
• 每个油井的第一条记录没有上一行参照数据，LAG函数返回null，最终hourly_rate为null（这种情况只会返回null不会触发报错）

修复方案 #

在做除法运算时，给分母hourly_rate也加上NULLIF处理，将0转换为null，整个运算结果就会返回null而非触发报错。如果业务需要空值返回0，可以在外层嵌套COALESCE函数处理。

修改后的完整SQL如下：

WITH query1 AS (
   SELECT well_id, produced_at, oil,
  (EXTRACT(EPOCH FROM age(produced_at,
                          LAG(produced_at) OVER w))/3600)::int as hourly_rate
                          FROM public.production
WINDOW w AS (PARTITION BY well_id ORDER BY well_id, produced_at 
             ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
     
),
query2 as (
   select Well_id, produced_at, nullif(oil,0), hourly_rate,
   (nullif(oil,0)/NULLIF(hourly_rate, 0)*6) as Last_Six_Hours_Of_Production,
   (nullif(oil,0)/NULLIF(hourly_rate, 0)*12) as Last_Twelve_Hours_Of_Production
    from query1
)
Select *
from query2; 

如果需要空值默认返回0，对应计算逻辑可以调整为：
COALESCE(nullif(oil,0)/NULLIF(hourly_rate, 0)*6, 0) as Last_Six_Hours_Of_Production

内容的提问来源于stack exchange，提问作者Justin Massaro