Postgres 13

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?.

CREATE TABLE scores
(
     sno 	int,
     grade 	int
);
INSERT INTO scores ( sno , grade )
VALUES
    (101, 64),
    (109, 68),
    (105, 75),
    (109, 76),
    (108, 78),
    (107, 79),
    (108, 81),
    (101, 85),
    (NULL, 86),
    (105, 88),
    (107, 91),
    (103, 92);
12 rows affected

select sno, MIN(grade) as mingrade, MAX(grade) AS maxgrade  
from scores 
where sno is not null 
group by sno
having min(grade) >= 70 and max(grade) <= 90


sno
mingrade
maxgrade


108
78
81

105
75
88


SELECT sno                              
FROM scores
GROUP BY sno
HAVING grade BETWEEN 70 and 90
ERROR:  column "scores.grade" must appear in the GROUP BY clause or be used in an aggregate function
LINE 4: HAVING grade BETWEEN 70 and 90
               ^


SELECT sno                              
FROM scores
where grade between 70 and 90
GROUP BY sno


sno


101

105

107

108

109

null


SELECT SNO, grade
FROM scores
WHERE 1=1
AND (GRADE > 70 AND GRADE < 90)


sno
grade


105
75

109
76

108
78

107
79

108
81

101
85

null
86

105
88