CTE query to root element postgres

Question

This is a very general question. I found some questions and discussions on more specific problems on SO, but I am quite sure, that many of you have already solved this one:

input:

1. A table that has a tree structure in one field.
2. An arbitrary id of a database record x.

question:

How can I get the root of the tree of x?

I found out that there should be a way to implement this recursively, but I couldn't achieve it yet.

Answer 1

The root element can be found in the same way as child elements of a given root,
but the query must search in the opposite direction.

Take a look at simple demo: --> http://www.sqlfiddle.com/#!17/fdc8a/1

This query retrieves all childrens of a given root:

WITH RECURSIVE childs( id, parent_id ) 
AS (
  -- get parent
  SELECT id, parent_id
  FROM tab
  WHERE id = 10

  UNION ALL

  -- get all children
  SELECT t.id, t.parent_id
  FROM childs c
  JOIN tab t
  ON t.parent_id = c.id
)
SELECT * from childs;

and this query retrieves all parents of a given child node:

WITH RECURSIVE parents( id, parent_id ) 
AS (
  -- get leaf children
  SELECT id, parent_id
  FROM tab
  WHERE id = 14

  UNION ALL

  -- get all parents  
  SELECT t.id, t.parent_id
  FROM parents p
  JOIN tab t
  ON p.parent_id = t.id
)
SELECT * from parents
-- WHERE parent_id is null;

if only the root node is needed, a clause WHERE parent_id IS NULL filters out all except the root.