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)

Edit your question, and paste create table and insert statements for PostgreSQL (at the very least) to reproduce your problem. From your images, it doesn't even look like Oracle and PostgreSQL have the same data. If they don't have the same data, different results are inevitable. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Dec 23, 2016 at 14:04
These queries are equivalent, they give exactly the same set of rows. The order of rows in both resultsets can be different, but this is correct, since by definition the table and the resultset in SQL have no any order unless the order by clause is used, see: en.wikipedia.org/wiki/Result_set. Because of this you don't see the same few rows at the top of both resultses. — krokodilko
– krokodilko, Commented Dec 23, 2016 at 15:42
@krokodilko While ordering may be an issue, the PG query is incorrectly specified. As it is now, it will never iterate. — Patrick
– Patrick, Commented Dec 23, 2016 at 15:46

Patrick · Accepted Answer · 2016-12-23 15:50:17Z

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;

edited Dec 23, 2016 at 15:50

answered Dec 23, 2016 at 15:41

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

krokodilko Over a year ago

WHERE sub_tree_id = 0 in CTE query is a couterpart of START WITH sub_tree_id = 0 in Oracle's CONNECT BY query - but there is no such a clause in the original query,

Patrick Over a year ago

@krokodilko Without a filter in the non-recursive term, there will be no iteration. That may be a difference between PG and Oracle, it is still required to reproduce the same output in PG.

krokodilko Over a year ago

Please glance over my answer, you will see why this query is wrong.

user330315 Over a year ago

@Patrick: the filter is not "required" to make a recursive query work in Postgres - but I agree that it is probably correct to add it. The original Oracle query might just be wrong there

Nayan Arora Over a year ago

Is it possible to maintain the same display order in postgreSQL as generated in oracle ?

|

Collectives™ on Stack Overflow

How to convert oracle hierarchical queries to postgresql?

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related