Postgresql:How to avoid zero-divisioin error in where section?

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?

Solution

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}

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;
```
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;
```
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.