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 tbl (id int, datetime timestamp, state text, value_needed text);
INSERT INTO tbl VALUES
(1, '2021-04-01 09:42:41.319000', 'incomplete', 'A')
, (2, '2021-04-04 09:42:41.319000', 'done' , 'B')
, (3, '2021-04-05 09:42:41.319000', 'incomplete', 'C')
, (4, '2021-04-05 10:42:41.319000', 'incomplete', 'C')
, (5, '2021-04-07 09:42:41.319000', 'done' , 'D')
, (6, '2021-04-12 09:42:41.319000', 'done' , 'E');
CREATE TABLE
INSERT 0 6
SELECT *
FROM tbl t1
WHERE NOT EXISTS (
SELECT FROM tbl t2
WHERE t2.state <> t1.state
AND t2.datetime > t1.datetime
)
ORDER BY datetime
LIMIT 1;
| id | datetime | state | value_needed |
|---|---|---|---|
| 5 | 2021-04-07 09:42:41.319 | done | D |
SELECT 1