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 course_prerequisites
(course_id int, prerequisite_course_id int)
;
INSERT INTO course_prerequisites
(course_id, prerequisite_course_id)
VALUES
(2, 1),
(3, 2),
(3, 5),
(4, 3),
(6, 4)
;
CREATE TABLE
INSERT 0 5
with recursive cte as (
select prerequisite_course_id
from course_prerequisites
where course_id = 6
union all
select cp.prerequisite_course_id
from cte join
course_prerequisites cp
on cte.prerequisite_course_id = cp.course_id
)
select *
from cte
| prerequisite_course_id |
|---|
| 4 |
| 3 |
| 2 |
| 5 |
| 1 |
SELECT 5