Migrate hierarchical queries from Oracle to PostgreSQL

This article explores what are the hierarchical queries and how to migrate it from Oracle and PostgreSQL.

Hierarchical query

In this context, hierarchy means relationship between parent and child tables or views provided that every child can have only one parent, whereas a parent can have multiple children. This is very useful when trying to build reporting queries. A hierarchical query is very helpful to organize extracted rows in a tree-like structure, where the data is retrieved by iterating from the root.

Oracle Syntax

Basically, hierarchical queries are created via these keywords in Oracle:

CONNECT BY - mandatory clause to specify parent/child relationship
PRIOR - mandatory indication of the parent
START WITH - condition defining where to begin the query results
LEVEL - meta-column indicating the hierarchy level or row position

The example below illustrates how a hierarchical query works in Oracle. Assume, we have the table with all employees of some company defining as:

create table employees(  
  id                int,  
  full_name         varchar2(50),  
  job_title         varchar2(20),  
  manager_id        int
);

The table contains data as follows:

insert into employees values(123, 'Mark',   'CEO',               null);
insert into employees values(29,  'Harry',  'CTO',               123);
insert into employees values(46,  'Paul',   'Software Engineer', 29);
insert into employees values(17,  'Sam',    'Software Engineer', 29);
insert into employees values(89,  'Dan',    'Software Engineer', 29);
insert into employees values(30,  'John',   'CFO',               123);
insert into employees values(74,  'Bill',   'Sales Manager',     30);
insert into employees values(51,  'Andrew', 'Sales Manager',     30);

Now it is required to build report containing position of each employee on the corporate ladder. This can easily be done through the following hierarchical query:

SQL> SELECT  id, full_name, job_title, level
FROM employees 
CONNECT BY PRIOR id = manager_id
START WITH manager_id IS NULL
order by level;

This query will extract the data below:

ID  FULL_NAME JOB_TITLE      LEVEL
----------------------------------
123 Mark      CEO                1
29  Harry     CTO                2
30  John      CFO                2
74  Bill      Sales Manager      3
51  Andrew    Sales Manager      3
46  Paul      Software Engineer  3
17  Sam       Software Engineer  3
89  Dan       Software Engineer  3
8 rows selected.

where LEVEL is meta-column indicating the hierarchy level starting with 1 (the root). In our case it also means position on the corporate ladder.

PostgreSQL Syntax

The same hierarchical query can be created in PostgreSQL with the help of common table expressions (CTE):

postgres=# WITH RECURSIVE cte AS (                                                                                                                                                          
	SELECT id, full_name, manager_id, 1 AS level                                                                                                                                             FROM   employees                                                                                                                                                                     
	where manager_id is null                                                                                                                                                            
	UNION  ALL                                                                                                                                                                               
	SELECT e.id, e.full_name, e.manager_id, c.level + 1                                                                                                                                      FROM   cte c                                                                                                                                                                            
	JOIN   employees e ON e.manager_id = c.id                                                                                  
)                                                                                                                                                                                     
SELECT *                                                                                                                                                                                 
FROM   cte;

     Id | full_name | manager_id | level 
--------+-----------+------------+------
    123 | Mark      |            |     1
     29 | Harry     |        123 |     2
     30 | John      |        123 |     2
     74 | Bill      |         30 |     2
     51 | Andrew    |         30 |     3
     46 | Paul      |         29 |     3
     17 | Sam       |         29 |     3
     89 | Dan       |         29 |     3
(8 rows)

As we can see from the example given above, parts of hierarchical query are migrated from Oracle from PostgreSQL as:

START WITH manager_id IS NULL becomes WHERE manager_id IS NULL
CONNECT BY PRIOR ... becomes JOIN employees ON ...
level column must be explicitly defined in PostgreSQL

More information about how to migrate Oracle queries to PostgreSQL is available in this whitepaper