Postgres 11

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 (id int primary key, amount numeric);
insert into t values
(1,1),(2,3),(3,5);
3 rows affected

with ct as
(
  select
      id,
      amount,
      (sum(amount) over (order by id rows between 2 preceding and 1 preceding)) / 2 as new_amount
  from
      t
)
update t
set    amount = ct.new_amount
from
    ct
where
    t.id = ct.id
    and ct.id = 3;
1 rows affected

select * from t;


id
amount


1
1

2
3

3
2.0000000000000000


select sum(amount) / count(*)
from (select amount from t where t.id < 3 order by id desc limit 2) t2


?column?


2.0000000000000000


update t
set amount = (select sum(t3.amount) / 2
             from (select amount 
                   from t t2
                   where t2.id < t.id 
                   order by id desc 
                   limit 2) t3)
where
    t.id = 3;
1 rows affected

select * from t;


id
amount


1
1

2
3

3
2.0000000000000000