This article explores what are the hierarchical queries and how to migrate it from Oracle and PostgreSQL.
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.
Basically, hierarchical queries are created via these keywords in Oracle:
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.
LEVEL is meta-column indicating the hierarchy level
starting with 1 (the root). In our case it also means position on
the corporate ladder.
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 NULLbecomes
WHERE manager_id IS NULL
CONNECT BY PRIOR ...becomes
JOIN employees ON ...
levelcolumn must be explicitly defined in PostgreSQL
More information about how to migrate Oracle queries to PostgreSQL is available in this whitepaper