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 mytable(
product_id INTEGER NOT NULL
,amount INTEGER NOT NULL
,date DATE NOT NULL
);
INSERT INTO mytable(product_id,amount,date) VALUES (1,100,'2019-01-01');
INSERT INTO mytable(product_id,amount,date) VALUES (2,150,'2019-01-01');
INSERT INTO mytable(product_id,amount,date) VALUES (1,200,'2019-01-05');
INSERT INTO mytable(product_id,amount,date) VALUES (2,180,'2019-01-03');
INSERT INTO mytable(product_id,amount,date) VALUES (2,150,'2019-01-05');
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
select
product_id,
dt,
first_value(amount) over(partition by product_id, grp order by dt) amount
from (
select
x.*,
t.amount,
count(*) filter(where t.amount is not null) over(partition by x.product_id order by x.dt) grp
from (
select product_id, generate_series(min(date), max(date), '1 day'::interval) dt
from mytable
group by product_id
) x
left join mytable t on t.product_id = x.product_id and t.date = x.dt
) t
order by product_id, dt
| product_id | dt | amount |
|---|---|---|
| 1 | 2019-01-01 00:00:00+00 | 100 |
| 1 | 2019-01-02 00:00:00+00 | 100 |
| 1 | 2019-01-03 00:00:00+00 | 100 |
| 1 | 2019-01-04 00:00:00+00 | 100 |
| 1 | 2019-01-05 00:00:00+00 | 200 |
| 2 | 2019-01-01 00:00:00+00 | 150 |
| 2 | 2019-01-02 00:00:00+00 | 150 |
| 2 | 2019-01-03 00:00:00+00 | 180 |
| 2 | 2019-01-04 00:00:00+00 | 180 |
| 2 | 2019-01-05 00:00:00+00 | 150 |