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?.
--https://stackoverflow.com/q/75462304/5298879
CREATE TABLE IF NOT EXISTS test (
id VARCHAR,
date DATE,
value_1 INT,
value_2 VARCHAR
);
INSERT INTO test VALUES
(1, '2022-01-04', 5, 'asdf'),
(1, '2022-01-03', NULL, NULL),
(1, '2022-01-02', NULL, 'def'),
(1, '2022-01-01', 4, NULL),
--(1, '2021-12-31', 5, NULL),
(2, '2022-01-04', 1, 'a'),
(2, '2022-01-03', NULL, NULL),
(2, '2022-01-02', 2, 'b'),
(2, '2022-01-01', NULL, NULL)
returning *;
CREATE TABLE
| id | date | value_1 | value_2 |
|---|---|---|---|
| 1 | 2022-01-04 | 5 | asdf |
| 1 | 2022-01-03 | null | null |
| 1 | 2022-01-02 | null | def |
| 1 | 2022-01-01 | 4 | null |
| 2 | 2022-01-04 | 1 | a |
| 2 | 2022-01-03 | null | null |
| 2 | 2022-01-02 | 2 | b |
| 2 | 2022-01-01 | null | null |
INSERT 0 8
--See what the `distinct on` finds for the row `(1, '2022-01-03', NULL, NULL)`
select distinct on (t2.id) * --The row on top (latest by date)
from test t2 --that's in the same table ^
where t2.date < '2022-01-03' --in an earlier row |
and t2.id = '1' --for the same id |
and t2.value_1 is not null --and isn't a null. |
order by t2.id, date desc --The top meaning the latest_/
| id | date | value_1 | value_2 |
|---|---|---|---|
| 1 | 2022-01-01 | 4 | null |
SELECT 1
update test t1
set value_1=(select distinct on (t2.id) value_1--Use the top (latest by date) value_1
from test t2 --that's in the same table ^
where t2.date < t1.date --in an earlier row |
and t2.id = t1.id --for the same id |
and t2.value_1 is not null --and isn't a null. |
order by t2.id, date desc) --The top meaning the latest_/
where t1.value_1 is null;
update test t1
set value_2=(select distinct on (t2.id) value_2--Use the top (latest by date) value_2
from test t2 --that's in the same table ^
where t2.date < t1.date --in an earlier row |
and t2.id = t1.id --for the same id |
and t2.value_2 is not null --and isn't a null. |
order by t2.id, date desc) --The top meaning the latest_/
where t1.value_2 is null;
select * from test order by id,date desc;
UPDATE 4
UPDATE 4
| id | date | value_1 | value_2 |
|---|---|---|---|
| 1 | 2022-01-04 | 5 | asdf |
| 1 | 2022-01-03 | 4 | def |
| 1 | 2022-01-02 | 4 | def |
| 1 | 2022-01-01 | 4 | null |
| 2 | 2022-01-04 | 1 | a |
| 2 | 2022-01-03 | 2 | b |
| 2 | 2022-01-02 | 2 | b |
| 2 | 2022-01-01 | null | null |
SELECT 8