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 rent(id integer,start_date date, end_date date,objekt_id integer,person_id integer);
INSERT INTO rent VALUES
(1, '2011-10-01','2015-10-31',5156,18268),
(2, '2015-11-01','2018-04-30',5156,18268),
(3, '2018-05-01','2021-03-31',5156,18269),
(4, '2021-04-01','2021-05-15',5156,null),
(5, '2021-05-16','2100-01-01',5156,18270),
(6, '2021-03-14','2021-05-15',5160,18270),
(7, '2021-05-16','2100-01-01',5160,18271);
7 rows affected
with tbl as (
SELECT rent.*,
row_number() over (PARTITION BY objekt_id) as row_id
FROM rent
ORDER BY id)
SELECT r.id,
r.start_date,
r.end_date,
r.objekt_id,
r.person_id,
( SELECT t1.person_id
FROM tbl t1
WHERE t1.objekt_id = r.objekt_id
AND t1.id < r.id
AND (t1.person_id <> r.person_id OR r.person_id IS NULL)
AND t1.person_id IS NOT NULL
ORDER BY t1.id desc
LIMIT 1) last_person,
(SELECT t1.person_id
FROM tbl t1
WHERE t1.objekt_id = r.objekt_id
AND t1.id > r.id
AND (t1.person_id <> r.person_id OR r.person_id IS NULL)
AND t1.person_id IS NOT NULL
ORDER BY t1.id
LIMIT 1) next_person
FROM tbl r
order by 1;
| id | start_date | end_date | objekt_id | person_id | last_person | next_person |
|---|---|---|---|---|---|---|
| 1 | 2011-10-01 | 2015-10-31 | 5156 | 18268 | null | 18269 |
| 2 | 2015-11-01 | 2018-04-30 | 5156 | 18268 | null | 18269 |
| 3 | 2018-05-01 | 2021-03-31 | 5156 | 18269 | 18268 | 18270 |
| 4 | 2021-04-01 | 2021-05-15 | 5156 | null | 18269 | 18270 |
| 5 | 2021-05-16 | 2100-01-01 | 5156 | 18270 | 18269 | null |
| 6 | 2021-03-14 | 2021-05-15 | 5160 | 18270 | null | 18271 |
| 7 | 2021-05-16 | 2100-01-01 | 5160 | 18271 | 18270 | null |