How to convert oracle hierarchical queries to postgresql?

Question

I want to convert below mentioned oracle hierarchical query to postgresql

SELECT catalog_id, sub_tree_id
FROM   my_catalog
CONNECT BY PRIOR catalog_id = sub_tree_id;

I have tried using the following postgresql query but not getting the expected result

WITH RECURSIVE q AS (
SELECT po.catalog_id,po.sub_tree_id
  FROM my_catalog po
UNION ALL
SELECT po.catalog_id,po.sub_tree_id
  FROM my_catalog po
  JOIN q ON q.catalog_id=po.sub_tree_id
)
SELECT * FROM q;

ORACLE OUTPUT(EXPECTED RESULT)

POSTGRESQL OUTPUT(ACTUAL RESULT)

Answer 1

In PostgreSQL recursive queries are constructed by first specifying the initial set of rows (the non-recursive term, i.e. those at the root or final level of the hierarchy). Subsequent iterations (over the recursive term, the sub-query after the UNION ALL) then add rows to the result set from the remaining rows in the input row set until no more rows are added.

In your case, the initial sub-query is unfiltered so you simply add all rows on the initial run, leaving nothing for subsequent runs.

Try the following:

WITH RECURSIVE q AS (
  SELECT po.catalog_id,po.sub_tree_id
  FROM my_catalog po
  WHERE sub_tree_id = 0  -- this initially selects only "root" rows
UNION ALL
  SELECT po.catalog_id,po.sub_tree_id
  FROM my_catalog po
  JOIN q ON q.catalog_id=po.sub_tree_id
)
SELECT * FROM q;