Bottom up recursive SQL example

recursive_sql_example.sql

-- A bottom-up recursive Postgres query on a table named 'family' 
-- with three columns: id, parent_id, child_id

WITH RECURSIVE bottom_up_family (
  parent_id, child_id, depth, path
) AS (
SELECT
fam_initial.parent_id,
fam_initial.child_id,
  1,
  ARRAY[fam_initial.child_id]
FROM family as fam_initial
WHERE child_id = 1000
UNION
SELECT
  fam_recurse.parent_id,
  fam_recurse.child_id,
  prev_query.depth + 1,
  path || fam_recurse.parent_id
FROM family fam_recurse
JOIN bottom_up_family prev_query
  ON prev_query.parent_id = fam_recurse.child_id
)

SELECT * FROM bottom_up_family