Question 390840 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – division by zero in and expression

One of methods to handle zero-division error is to use case when structure.

set start  '2024-05-20'
set end    '2024-05-31'
set change (q2.close-q1.close)/q1.close

select   q1.ticker,q1.date,
         case when
             q1.close=0 then null 
         else
             :change end
from quote q1,quote q2
where
    q1.ticker=q2.ticker
and    q1.date= :'start'
and    q2.date= :'end';

To use case when structure ,zero-division error avoided.
Now i want to get all the result whose change value is greater than 0.3.

select   q1.ticker,q1.date,
         case when
             q1.close=0 then null 
         else
             :change end
from quote q1,quote q2
where
    q1.ticker=q2.ticker
and    q1.date= :'start'
and    q2.date= :'end'
and :change > 0.3;

It got the error info:

ERROR:  division by zero
CONTEXT:  parallel worker

How can write the postgresql select statement then?

Tags:

2

Answers


  1. 0

    You can’t define an operation in a variable set change (q2.close-q1.close)/q1.close and then expect that it will be somehow replaced on the query. You just do the operation on the query itself and for the > 0.3 you also use the case statement on the where clause (there are other ways of course).

    Also you should use the standard join operation, the one you are using is way outdated

    select   q1.ticker,q1.date,
         case when
             q1.close=0 then null 
         else
             (q2.close-q1.close)/q1.close end
from quote q1 
   join quote q2 on q1.ticker=q2.ticker
where  q1.date= :'start'
  and  q2.date= :'end'
  and case when
       q1.close=0 then 0 
      else
       (q2.close-q1.close)/q1.close end > 0.3;
    Login or Signup to reply.
  2. 0

    Your first example attempts to only evaluate the division in your select list when it’s safe.
    Your second example does the same thing in the select list but then it tries to run the division for all rows in your where section, without that safety logic. It evaluates to this:

    select   q1.ticker,q1.date,
         case when
             q1.close=0 then null 
         else
             (q2.close-q1.close)/q1.close end
from quote q1 join quote q2 using (ticker)
where  q1.date= '2024-05-20'
and    q2.date= '2024-05-31'
--this is evaluated for all rows, without checking for division-by-0-safety:
and (q2.close-q1.close)/q1.close > 0.3;

    You could correct it more than one way: demo at db<>fiddle

    1. Add another condition to only run the division when it’s safe. 
      and (q2.close-q1.close)/q1.close > 0.3
and q1.close<>0;
    2. Repeat that case statement in your where section, the same way you did in your select section. Rows that go down the null case branch will be discarded by where: 
      and case when q1.close=0 then null else (q2.close-q1.close)/q1.close end>0.3;
    3. Build that safety right into your equation and keep your current query unchanged. Use nullif() 
      set change (q2.close-q1.close)/nullif(q1.close,0)

    The quote table is joined with itself so you can join...using(ticker) thanks to the matching column name.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search