4

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)

oracle output

POSTGRESQL OUTPUT(ACTUAL RESULT)

postgress output

Nayan Arora
  • 79
  • 1
  • 3
  • 1
    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' Dec 23 '16 at 14:04
  • 1
    Can you please show how original data look like? – JosMac Dec 23 '16 at 15:35
  • 1
    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: https://en.wikipedia.org/wiki/Result_set. Because of this you don't see the same few rows at the top of both resultses. – krokodilko Dec 23 '16 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 Dec 23 '16 at 15:46
  • http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 –  Dec 23 '16 at 15:58

1 Answers1

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;
Patrick
  • 29,357
  • 6
  • 62
  • 90
  • 1
    `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, – krokodilko Dec 23 '16 at 15:49
  • @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. – Patrick Dec 23 '16 at 15:51
  • Please glance over my answer, you will see why this query is wrong. – krokodilko Dec 23 '16 at 16:33
  • @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 –  Dec 23 '16 at 21:45
  • Is it possible to maintain the same display order in postgreSQL as generated in oracle ? – Nayan Arora Jan 04 '17 at 05:12
  • Ordering is not guaranteed in any RDBMS unless a specific `ORDER BY` clause is included. The internal structures of Oracle determine the default order in which the rows are presented and in PG those internals apparently work differently. Quite possibly a different version of Oracle would also produce a different order. – Patrick Jan 04 '17 at 08:40