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 my_table as
select 'Chemistry' as subject, '2021-02-04'::date as attendance_date, 'Chris' as student union all
select 'Chemistry' as subject, '2021-02-04'::date as attendance_date, 'Joseph' as student union all
select 'English ' as subject, '2021-02-04'::date as attendance_date, 'Chris' as student union all
select 'English ' as subject, '2021-02-04'::date as attendance_date, 'Joseph' as student union all
select 'Chemistry' as subject, '2021-02-05'::date as attendance_date, 'Chris' as student union all
select 'Chemistry' as subject, '2021-02-05'::date as attendance_date, 'Joseph' as student union all
select 'English ' as subject, '2021-02-05'::date as attendance_date, 'Joseph' as student union all
select 'Chemistry' as subject, '2021-02-07'::date as attendance_date, 'Chris' as student union all
select 'Chemistry' as subject, '2021-02-07'::date as attendance_date, 'Joseph' as student union all
select 'English ' as subject, '2021-02-07'::date as attendance_date, 'Chris' as student union all
select 'English ' as subject, '2021-02-07'::date as attendance_date, 'Joseph' as student
11 rows affected
select d.date, su.subject, st.student,
(t.student is not null) as attended_flag
from generate_series('2021-02-04', '2021-02-07', interval '1 day') d(dte) cross join
(select distinct subject from my_table) su cross join
(select distinct student from my_table) st left join
my_table t
on t.student = st.student and t.subject = su.subject and
t.attendance_date = d.dte;
| date | subject | student | attended_flag |
|---|---|---|---|
| 2021-02-04 | English | Chris | t |
| 2021-02-07 | English | Chris | t |
| 2021-02-06 | English | Chris | f |
| 2021-02-05 | English | Chris | f |
| 2021-02-04 | Chemistry | Chris | t |
| 2021-02-05 | Chemistry | Chris | t |
| 2021-02-07 | Chemistry | Chris | t |
| 2021-02-06 | Chemistry | Chris | f |
| 2021-02-04 | English | Joseph | t |
| 2021-02-05 | English | Joseph | t |
| 2021-02-07 | English | Joseph | t |
| 2021-02-06 | English | Joseph | f |
| 2021-02-04 | Chemistry | Joseph | t |
| 2021-02-05 | Chemistry | Joseph | t |
| 2021-02-07 | Chemistry | Joseph | t |
| 2021-02-06 | Chemistry | Joseph | f |