Postgres 14

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

with my_table (id, student, max_score, year_1, year_2, year_3, year_4, year_5)
as (values 
(1, 'Alex', 7,null, 7, 7, null, 7),
(2, 'Bruno', 10, 10, 7, 10, 9, 10),
(3, 'Charlie', 7, 7, 5, null, 7, 7),
(4, 'Dan', 3,null, 3, 3, 3, null)
),

score_check as
(select *, 
   case when (
     --Check if there is a gap by making a concatenating
     --the values for all years, replacing nulls with an 
     --'x'. Use a regular expression to check if it matches
     --a pattern looking like digit-character-digit
     coalesce(year_1::character, 'x')||
     coalesce(year_2::character, 'x')||
     coalesce(year_3::character, 'x')||
     coalesce(year_4::character, 'x')||
     coalesce(year_5::character, 'x') ~ '\d+x\d+') 
     --Check if all scores are equal to the max_score
     or
     (
     year_1 <> max_score or
     year_2 <> max_score or
     year_3 <> max_score or
     year_4 <> max_score or
     year_5 <> max_score 
     )
     then false else true end is_a_match
from my_table
)

select *
from score_check
where is_a_match is false

id	student	max_score	year_1	year_2	year_3	year_4	year_5	is_a_match
1	Alex	7	null	7	7	null	7	f
2	Bruno	10	10	7	10	9	10	f
3	Charlie	7	7	5	null	7	7	f