This article explains how you can migrate Oracle hierarchical queries to PostgreSQL equivalent.

Oracle database provides CONNECT BY, START WITH clauses to operate on hierarchical data. LEVEL clause in Oracle database helps you build dynamic hierarchy and also gives you the depth of hierarchy. LEVEL clause is very important consideration to generate “N” number of rows for a certain condition. You can learn in depth of hierarchical query in our documentation here.

PostgreSQL does not have hierarchical CONNECT BY queries. PostgreSQL provides WITH RECURSIVE clause which is an alternate option for hierarchical queries. All of Oracle CONNECT BY, START WITH queries can be converted to PostgreSQL equivalent using WITH RECURSIVE clause.

Migrating Oracle recursive queries in PostgreSQL

Oracle provides CONNECT BY clause to operate on hierarchical data. Oracle also supports recursive subquery factoring from 11g release 2. If you belong to Oracle database competency, you must be familiar with Oracle CONNECT BY clause and WITH clause hierarchical subquery factoring. Read CONNECT BY and WITH Recursive queries in Oracle article which provides understanding of Oracle hierarchical queries using CONNECT BY and how the same query can be rewritten using recursive subquery factoring.

Migrating Oracle CONNECT BY queries to Oracle becomes much simpler if you are good with understanding of Oracle recursive subquery factoring. Let’s take few examples and check how query containing CONNECT BY clause can be converted using WITH clause subquery factoring and then migrated to PostgreSQL.

Q : Generate numbers from 1 to 10.

Ans:

SELECT LEVEL AS num
FROM DUAL
CONNECT BY LEVEL <= 10;

WITH cte(n) AS(
  SELECT 1 AS n FROM DUAL
  UNION ALL
  SELECT n+1 FROM cte
   WHERE n+1 <= 10
)
SELECT n AS num FROM cte;

WITH RECURSIVE cte(n) AS(
  SELECT 1 AS n FROM DUAL
  UNION ALL
  SELECT n+1 FROM cte
   WHERE n+1 <= 10
)
SELECT n AS num FROM cte;

SELECT n AS num
  FROM generate_series(1, 10) n;

Migrating hierarchical queries from Oracle can sometimes be simpler using generate_series() function in PostgreSQL. Our article Writing queries using generate_series() in PostgreSQL database unfolds the power of generate_series() function and the ease of using it for generating numbers or increment/decrement numbers or dates.

Let’s consider one more scenario.

Q : Get the dates between ’12-MAR-2019′ and ’20-MAR-2019′.

Ans:

SELECT to_date('2019-03-12', 'YYYY-MM-DD')+LEVEL-1 AS num
FROM DUAL
CONNECT BY to_date('2019-03-12', 'YYYY-MM-DD')+LEVEL-1 <= to_date('2019-03-20', 'YYYY-MM-DD');

WITH cte(days) AS(
  SELECT to_date('2019-03-12', 'YYYY-MM-DD') as days FROM DUAL
  UNION ALL
  SELECT days+1 FROM cte
   WHERE days+1 <= to_date('2019-03-20', 'YYYY-MM-DD')
)
SELECT days FROM cte;

WITH RECURSIVE cte(days) AS(
  SELECT '2019-03-12'::date as days
  UNION ALL
  SELECT days+1 FROM cte
   WHERE days+1 <= '2019-03-20'::date
)
SELECT days FROM cte;

SELECT * from generate_series('2019-03-12'::timestamp, '2019-03-20'::timestamp, interval '1 day') days;

Hope you got the basics of different approaches to hierarchical queries and how PostgreSQL generate_series() function can save lot of effort and simply code if generating sequence numbers. Read our article on generate_series() in PostgreSQL for getting detail into how generate_series() can save a lot of coding effort.

Steps to migrate Oracle CONNECT BY queries to PostgreSQL

Migrating Oracle hierarchical queries involving CONNECT BY and START WITH can sometimes be painstaking and tedious. It is utmost important to understand the execution of START WITH and CONNECT BY clauses in Oracle before converting them using WITH RECURSIVE clause.

Get detailed explanation of step to convert Oracle CONNECT BY queries to WITH RECURSIVE clause in CONNECT BY and WITH Recursive queries.

Nothing stops you learn more. Oracle database gives you flexibility of writing hierarchical queries using Oracle proprietary features – START WITH and CONNECT BY clauses or you can even use WITH clause. Choose the option you are comfortable with. However we bring you some more examples in Tricky questions on CONNECT BY, START WITH and LEVEL clause.