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?.
select version();
| version |
|---|
| PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
SELECT 1
create table test (need varchar(255), responsibility varchar(255) null);
CREATE TABLE
insert into test values ('Grounds Keeping',null),
('Tree/shrub maintenance','Parks and Recreation'),
('Tree Removal','Public Works'),
('Mowing','Parks and Recreation'),
('Fire Station Mowing','Fire'),
('Irrigation Systems','Parks and Recreation'),
('Retaining Walls/Slope Maint.','Capital Projects'),
('Building Maintenance',null),
('Cleaning/Resupply','Building Occupant'),
('Electrical','Facility Maintenance'),
('Carpet Maintenance','Facility Operations'),
('Generators','Facility Maintenance'),
('Appliances','Facility Maintenance');
INSERT 0 13
select * from (WITH cte AS
(SELECT *,
MAX( CASE WHEN category IS NOT NULL THEN rn END )
OVER( ORDER BY rn
ROWS UNBOUNDED PRECEDING ) AS grp
FROM (SELECT row_number() OVER (
ORDER BY NULL
) AS rn
,responsibility
,CASE
WHEN responsibility IS NULL
THEN NULL
ELSE need
END AS _need
,CASE
WHEN responsibility IS NULL
THEN need
END AS category
FROM test) as t2)
SELECT MAX(category) OVER( PARTITION BY grp
ORDER BY rn
ROWS UNBOUNDED PRECEDING ) AS _category, _need, responsibility
FROM cte) as t3 where responsibility is not null;
| _category | _need | responsibility |
|---|---|---|
| Grounds Keeping | Tree/shrub maintenance | Parks and Recreation |
| Grounds Keeping | Tree Removal | Public Works |
| Grounds Keeping | Mowing | Parks and Recreation |
| Grounds Keeping | Fire Station Mowing | Fire |
| Grounds Keeping | Irrigation Systems | Parks and Recreation |
| Grounds Keeping | Retaining Walls/Slope Maint. | Capital Projects |
| Building Maintenance | Cleaning/Resupply | Building Occupant |
| Building Maintenance | Electrical | Facility Maintenance |
| Building Maintenance | Carpet Maintenance | Facility Operations |
| Building Maintenance | Generators | Facility Maintenance |
| Building Maintenance | Appliances | Facility Maintenance |
SELECT 11