By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
--https://stackoverflow.com/q/78568907/5298879
create table quote(ticker,date,close)as values
(1,'2024-05-20'::date,1)
,(1,'2024-05-31'::date,1)
,(2,'2024-05-20'::date,0)
,(2,'2024-05-31'::date,3)
,(3,'2024-05-20'::date,0)
,(3,'2024-05-31'::date,0)
,(4,'2024-05-20'::date,1)
,(4,'2024-05-31'::date,4);
SELECT 8
/*--OP's original code
\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';
*/
select q1.ticker,q1.date,
case when
q1.close=0 then null
else
(q2.close-q1.close)/q1.close end
from quote q1,quote q2
where
q1.ticker=q2.ticker
and q1.date= '2024-05-20'
and q2.date= '2024-05-31';
| ticker | date | case |
|---|---|---|
| 1 | 2024-05-20 | 0 |
| 2 | 2024-05-20 | null |
| 3 | 2024-05-20 | null |
| 4 | 2024-05-20 | 3 |
SELECT 4
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;
ERROR: division by zero
--Add another condition to only run the division when its safe.
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'
and (q2.close-q1.close)/q1.close > 0.3
and q1.close<>0;
| ticker | date | case |
|---|---|---|
| 4 | 2024-05-20 | 3 |
SELECT 1
--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:
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'
and case when q1.close=0 then null else (q2.close-q1.close)/q1.close end>0.3;
| ticker | date | case |
|---|---|---|
| 4 | 2024-05-20 | 3 |
SELECT 1
--Build that safety right into your equation and keep your current query unchanged.
--Use nullif()
select q1.ticker,q1.date,
(q2.close-q1.close)/nullif(q1.close,0)
from quote q1 join quote q2 using (ticker)
where q1.date= '2024-05-20'
and q2.date= '2024-05-31'
and (q2.close-q1.close)/nullif(q1.close,0) > 0.3;
| ticker | date | ?column? |
|---|---|---|
| 4 | 2024-05-20 | 3 |
SELECT 1