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 property as
select 1 as runid, 123 as listingid, 'abc' as listingname union all
select 1, 234, 'def' union all
select 2, 123, 'abcd' union all
select 2, 567, 'ghi' union all
select 2, 234, 'defg'
5 rows affected
-- updates
select p.*
from property p
where exists (select 1
from property p2
where p2.listingid = p.listingid and
p2.runid < p.runid
)
| runid | listingid | listingname |
|---|---|---|
| 2 | 123 | abcd |
| 2 | 234 | defg |
-- new
select p.*
from property p
where not exists (select 1
from property p2
where p2.listingid = p.listingid and
p2.runid < p.runid
)
| runid | listingid | listingname |
|---|---|---|
| 1 | 123 | abc |
| 1 | 234 | def |
| 2 | 567 | ghi |