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 table1(id,date,quantity,"value")as values
(1,'2024-10-01',1,1)
,(2,'2024-10-02',1,1)
,(3,'2024-10-03',1,1)
,(4,'2024-10-04',1,1)
,(5,'2024-10-05',1,1)
,(6,'2024-10-06',1,1)
,(7,'2024-10-07',1,1)
;
SELECT 7
begin;
with new_state as (
select id
, sum(quantity)over(order by date,id) new_value
from table1)
update table1
set value=new_value
from new_state
where table1.id=new_state.id;
table table1;
rollback;
BEGIN
UPDATE 7
| id | date | quantity | value |
|---|---|---|---|
| 1 | 2024-10-01 | 1 | 1 |
| 2 | 2024-10-02 | 1 | 2 |
| 3 | 2024-10-03 | 1 | 3 |
| 4 | 2024-10-04 | 1 | 4 |
| 5 | 2024-10-05 | 1 | 5 |
| 6 | 2024-10-06 | 1 | 6 |
| 7 | 2024-10-07 | 1 | 7 |
SELECT 7
ROLLBACK