Postgres 12

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 '2020-07-24'::date as date,  'Item A, Item B, Item C, Item D' as items union all                                
select '2020-07-27'::date as date, 'Item A, Item B, Item C, Item D, Item E, Item F' as items union all
select '2020-07-28'::date as date, 'Item A, Item D, Item E, Item F, Item G' 
3 rows affected

select date, items,
       string_agg( case when prev_date = prev_item_date then null else item end, ', ') as new_items,
       string_agg( case when next_date = next_item_date then null else item end, ', ') as removed_items
from (select t.*, item,
             lag(date) over (partition by item order by date) as prev_item_date,
             lead(date) over (partition by item order by date) as next_item_date
      from (select t.*, 
                   lag(date) over (order by date) as prev_date,
                   lead(date) over (order by date) as next_date
            from t
           ) t cross join
           regexp_split_to_table(t.items, ', ') item
     ) ti
group by date, items


date
items
new_items
removed_items


2020-07-24
Item A, Item B, Item C, Item D
Item A, Item B, Item C, Item D
null

2020-07-27
Item A, Item B, Item C, Item D, Item E, Item F
Item E, Item F
Item B, Item C

2020-07-28
Item A, Item D, Item E, Item F, Item G
Item G
Item F, Item A, Item G, Item E, Item D


select *
from (select t.*, item,
             lag(date) over (partition by item order by date) as prev_item_date,
             lead(date) over (partition by item order by date) as next_item_date
      from (select t.*, 
                   lag(date) over (order by date) as prev_date,
                   lead(date) over (order by date) as next_date
            from t
           ) t cross join
           regexp_split_to_table(t.items, ', ') item
     ) ti


date
items
prev_date
next_date
item
prev_item_date
next_item_date


2020-07-24
Item A, Item B, Item C, Item D
null
2020-07-27
Item A
null
2020-07-27

2020-07-27
Item A, Item B, Item C, Item D, Item E, Item F
2020-07-24
2020-07-28
Item A
2020-07-24
2020-07-28

2020-07-28
Item A, Item D, Item E, Item F, Item G
2020-07-27
null
Item A
2020-07-27
null

2020-07-24
Item A, Item B, Item C, Item D
null
2020-07-27
Item B
null
2020-07-27

2020-07-27
Item A, Item B, Item C, Item D, Item E, Item F
2020-07-24
2020-07-28
Item B
2020-07-24
null

2020-07-24
Item A, Item B, Item C, Item D
null
2020-07-27
Item C
null
2020-07-27

2020-07-27
Item A, Item B, Item C, Item D, Item E, Item F
2020-07-24
2020-07-28
Item C
2020-07-24
null

2020-07-24
Item A, Item B, Item C, Item D
null
2020-07-27
Item D
null
2020-07-27

2020-07-27
Item A, Item B, Item C, Item D, Item E, Item F
2020-07-24
2020-07-28
Item D
2020-07-24
2020-07-28

2020-07-28
Item A, Item D, Item E, Item F, Item G
2020-07-27
null
Item D
2020-07-27
null

2020-07-27
Item A, Item B, Item C, Item D, Item E, Item F
2020-07-24
2020-07-28
Item E
null
2020-07-28

2020-07-28
Item A, Item D, Item E, Item F, Item G
2020-07-27
null
Item E
2020-07-27
null

2020-07-27
Item A, Item B, Item C, Item D, Item E, Item F
2020-07-24
2020-07-28
Item F
null
2020-07-28

2020-07-28
Item A, Item D, Item E, Item F, Item G
2020-07-27
null
Item F
2020-07-27
null

2020-07-28
Item A, Item D, Item E, Item F, Item G
2020-07-27
null
Item G
null
null

date	items	new_items	removed_items
2020-07-24	Item A, Item B, Item C, Item D	Item A, Item B, Item C, Item D	null
2020-07-27	Item A, Item B, Item C, Item D, Item E, Item F	Item E, Item F	Item B, Item C
2020-07-28	Item A, Item D, Item E, Item F, Item G	Item G	Item F, Item A, Item G, Item E, Item D