Get Entire Hierarchy of Parents From a Given Child in Postgresql

Question

Here is a sample of the data I am looking at:

Table:

id     |    name    |   parent_id
_______|____________|______________
1      |Root        | null
2      |Parent #2   | 1
3      |Parent #3   | 1
4      |Parent #4   | 2
5      |Child #5    | 2
6      |Child #6    | 2
7      |Child #7    | 3
8      |Child #8    | 3
9      |Child #9    | 3

Using a recursive query, I am able to start from a Parent, and get all associated children.

My question is, how can I start at the child, and get all related parents, grandparents, etc, right up to the root.

So, given Child #9, I would like a query that returns the following:

id     |    name    |   parent_id
_______|____________|______________
1      |Root        | 0
3      |Parent #3   | 1
9      |Child #9    | 3

Any help would be greatly appreciated.

Answer 1

Like this:

with recursive whosYourDaddy as (

  --start with the "anchor" row
  select
    *
  from foo
  where
    id = 9 --parameterize me

  union all

  select
    foo.*
  from foo 
  join whosYourDaddy on whosYourDaddy.parent_id = foo.id
)

select
  *
from whosYourDaddy 
order by
  id;

Result:

id  name      parent_id
-----------------------
1   Root      (null)
3   Parent 3  1
9   Child 9   3

Code:

http://sqlfiddle.com/#!15/a5fb9/12