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 t as
select to_date('12/08/2020', 'DD/MM/YYYY') as date, 'David ' as name, 'Work ' as subject, 1 as importance, 'London ' as location, '- ' as time, null as rating union all
select to_date('1/08/2020 ', 'DD/MM/YYYY') as date, 'David ' as name, 'Work ' as subject, 3 as importance, 'London ' as location, '23.50' as time, 4 as rating union all
select to_date('2/10/2018 ', 'DD/MM/YYYY') as date, 'David ' as name, 'Emails ' as subject, 3 as importance, 'New York' as location, '18.20' as time, 3 as rating union all
select to_date('12/08/2020', 'DD/MM/YYYY') as date, 'George' as name, 'Updates ' as subject, 2 as importance, 'New York' as location, '- ' as time, null as rating union all
select to_date('1/08/2019 ', 'DD/MM/YYYY') as date, 'George' as name, 'New Appointments' as subject, 5 as importance, 'London ' as location, '55.10' as time, 2 as rating union all
select to_date('2/10/2019 ', 'DD/MM/YYYY') as date, 'David ' as name, 'Emails ' as subject, 3 as importance, 'Paris ' as location, '18.58' as time, 3 as rating union all
select to_date('8/09/2017 ', 'DD/MM/YYYY') as date, 'David ' as name, 'Emails ' as subject, 2 as importance, 'Paris ' as location, '18.90' as time, 5 as rating
SELECT 7
select t.*
from (select t.*,
count(*) filter (where date = current_date + interval '1 day') over (partition by name order by date rows between 1 following and 3 following) as cnt_tomorrow
from t
) t
where date = current_date + interval '1 day' or
cnt_tomorrow > 0
order by name, date;
| date | name | subject | importance | location | time | rating | cnt_tomorrow |
|---|
SELECT 0