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', 99, 'zz'),--id 1's max value1 and max value2
(2, '2022-01-04', 1, 'a'),
(2, '2022-01-03', NULL, NULL),
(2, '2022-01-02', 2, 'b'),
--(2, '2022-01-01', 77, 'yy'),--id 2's max value1 and max value2
(2, '2021-12-31', NULL, NULL);

SELECT *
FROM test
ORDER BY id, date DESC;

CREATE TABLE



INSERT 0 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
2021-12-31
null
null



SELECT 8


SELECT id,
       date,
       (JSONB_AGG(value_1)FILTER(WHERE value_1 IS NOT NULL)OVER w->>-1)::int AS value_1,
       (JSONB_AGG(value_2)FILTER(WHERE value_2 IS NOT NULL)OVER w->>-1) AS value_2
FROM test
WINDOW w AS (PARTITION BY id ORDER BY date) 
ORDER BY id, date DESC;


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
2021-12-31
null
null



SELECT 8


--https://stackoverflow.com/a/75631505/5298879
update test t1 
set value_1=(select distinct on (t2.id) value_1--The top value_1 (latest by date)
             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
2021-12-31
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	2021-12-31	null	null