Postgres 15

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

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