CODEWITHSUNDEEP
postgresqldivide-by-zero
Justin Massaro
Justin Massaro

Reputation: 59

postgresql division by zero error handling

The oil value sometimes is null. I need a division by zero exception and the null if operator is still giving this error code. Not sure what is wrong with the syntax.
ERROR: division by zero SQL state: 22012

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)/hourly_rate*6) as Last_Six_Hours_Of_Production,
   (nullif(oil,0)/hourly_rate*12) as Last_Twelve_Hours_Of_Production
    from query1
)
Select *
from query2;

Upvotes: 2

Views: 5038

Answers (1)

Anthony Sotolongo
Anthony Sotolongo

Reputation: 1648

I think that you need to add some NULLIF validation to the denominator too and add a COALESCE clause.

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,
   COALESCE((nullif(oil,0)/nullif(hourly_rate*6,0)),0) as Last_Six_Hours_Of_Production,
   COALESCE((nullif(oil,0)/nullif(hourly_rate*12,0)),0) as Last_Twelve_Hours_Of_Production
    from query1
)
Select *
from query2;

Upvotes: 4

Related Questions