How to run hierarchical queries in Oracle and PostgreSQL

January 24, 2023

SUMMARY: This article introduces hierarchical queries and shows the differences in their usage between Oracle and PostgreSQL.

1. Hierarchical query

2. Hierarchical query in Oracle

3. Displaying the levels of the records

4. Hierarchical query in PostgreSQL

5. Order by siblings

 

In this post, we are going to explain in brief about what a hierarchical query is in Oracle and then how to write such queries in PostgreSQL. 

Hierarchical query 

Hierarchy is based on relationship with a parent-child in the table or view. It mandates that each 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 displays organized rows in a tree structure, so in order to retrieve the data it has to be traversed starting from the root.

Hierarchical query in Oracle

Hierarchical queries make use of the following syntax, keywords, and clauses:

CONNECT BY: Defines the relationship between parent and child. This is a mandatory clause.

PRIOR: Indicate the parent. 

START WITH: Defines the records where we want the query to start. 

LEVEL: A pseudocolumn that indicates the level of hierarchy or row position.

connect_by_root: Basically returns the rows using data from the root row. 

ORDER SIBLINGS BY: Applies the ORDER BY clause to the siblings rows. 

Example

To understand how a hierarchical query works, let’s go through an example in Oracle.

To start, create a dummy table:

create table dummy_table(  

  emp_no                int,  

  ename                 char(5),  

  job                       char(9),  

  manager_no        int

);

 

Insert data into “dummy_table”:

insert into dummy_table values(10,'A1','CEO',null);

insert into dummy_table values(11, 'B1', 'VP', 10);

insert into dummy_table values(12, 'B2', 'VP', 10);

insert into dummy_table values(13, 'B3', 'VP', 10);

insert into dummy_table values(14, 'C1', 'DIRECTOR', 13);

insert into dummy_table values(15, ‘C2’, ‘DIRECTOR’, 13);

insert into dummy_table values(16, 'D1', 'MANAGER', 15);

insert into dummy_table values(17 ,'E1', 'ENGINEER', 11);

insert into dummy_table values(18, 'E2', 'ENGINEER', 11);

 

If we look at the tree structure of this table, we can easily identify who reports to whom:

 

A1 is the boss (root). This position doesn’t report to any one, but B1, B2, B3 (parents and children) report to it.

E1 and E2 report to B1. D1 reports to C2, and C2 and C1 report to B3 . 

Displaying the levels of the records 

We can make a simple hierarchical query to see the levels of the records in “dummy_table”:

SQL> SELECT  emp_no,ename,job,level

FROM dummy_table

CONNECT BY PRIOR emp_no = manager_no

START WITH manager_no IS NULL

order by level ;





    EMP_NO ENAME JOB   LEVEL

---------- ----- --------- -----------------

10 A1 CEO     1

11 B1  VP     2

12 B2 VP     2

13 B3 VP     2

15 C2 DIRECTOR    3

14 C1 DIRECTOR    3

18 E2 ENGINEER    3

17 E1 ENGINEER    3

16 D1 MANAGER     4

9 rows selected.

 

The LEVEL pseudocolumn returns the current depth in the tree, starting with 1. A1 is the root and has level=1. 

Hierarchical query in PostgreSQL 

We can create the same hierarchical query in PostgreSQL. Using best practices, there are two possible ways to do this:

1. With the help of common table expressions (CTE):

postgres=# WITH RECURSIVE cte AS (                                                                                                                                                          
SELECT emp_no, ename, manager_no, 1 AS level                                                                                                                                             FROM   dummy_table                                                                                                                                                                     
where manager_no is null                                                                                                                                                            
UNION  ALL                                                                                                                                                                               
SELECT e.emp_no, e.ename, e.manager_no, c.level + 1                                                                                                                                      FROM   cte c                                                                                                                                                                            
JOIN   dummy_table e ON e.manager_no = c.emp_no                                                                                  
)                                                                                                                                                                                     
SELECT *                                                                                                                                                                                 
FROM   cte;
 emp_no | ename | manager_no | level 
--------+-------+------------+-------
     10 | A1    |            |      1
     11 | B1    |         10 |     2
     12 | B2    |         10 |     2
     13 | B3    |         10 |     2
     14 | C1    |         13 |     3
     17 | E1    |         11 |     3
     18 | E2    |         11 |     3
     15 | C2    |         13 |     3
     16 | D1    |         15 |     4
(9 rows)

postgres=# 

 

Please note the PostgreSQL terms in this query: 

“WHERE manager_no …”  is equivalent to the Oracle command “START WITH” 

“JOIN dummy_table ON …”  is equivalent to the Oracle command “CONNECT BY.” 

2. Installing the extension tablefunc:

The tablefunc extension is a contrib module that resides under the contrib/ folder it PostgreSQL sources.This extension supports equivalent functions to CONNECT BY and STARTS WITH, as well as a LEVEL keyword, but the syntax is different from Oracle.

Create extension tablefunc: 

postgres=# CREATE EXTENSION tablefunc;

CREATE EXTENSION

postgres=# 

 

The connectby function, from which a hierarchical tree structure can be created, has the following structure: 

connectby(text relname, text keyid_fld, text parent_keyid_fld [, text orderby_fld ], text start_with, int max_depth [, text branch_delim ])

 

These are the parameters of the connectby function: 

relname:  Defines the name of the source relation (table)

keyid_fld: Name of the key field

parent_keyid_fld:  Name of the parent-key field

orderby_fld: Name of the field to order siblings by (optional)

start_with: Value of the row where the query should start

max_depth: Max depth, or zero for unlimited depth

branch_delim: String to separate keys with in branch output (optional)

Example

We can use the connectby function to display data in a hierarchical way:

postgres=# SELECT * FROM connectby('dummy_table', 'emp_no', 'manager_no', '10', 0, '->') AS t(emp_no int, manager_no int, level int, ord text) order by emp_no;

 emp_no | manager_no | level |      ord       

--------+------------+-------+----------------

     10 |                       |      0        | 10

     11 |              10     |      1        | 10->11

     12 |              10     |      1        | 10->12

     13 |              10     |      1        | 10->13

     14 |              13     |      2        | 10->13->14

     15 |              13     |      2        | 10->13->15

     16 |              15     |      3        | 10->13->15->16

     17 |              11     |      2        | 10->11->17

     18 |              11     |      2        | 10->11->18

(9 rows)



postgres=# 

 

Ordering by siblings

Let’s say we have the following query using ORDER BY in Oracle:

SQL>SELECT  emp_no,ename,job,level

FROM dummy_table

CONNECT BY PRIOR emp_no = manager_no

START WITH manager_no IS NULL

order siblings by emp_no ;

 

In PostgreSQL, the query can be rewritten like this:

postgres=# SELECT * FROM connectby('dummy_table', 'emp_no', 'manager_no', 'emp_no', '10', 0,'->') AS t(emp_no int, manager_no int, level int, branch text, ord int);

 emp_no | manager_no | level |   branch    |       ord

--------+------------+-------+-------------+------------------------

     10 |              |                 0 | 10                       |   1

     11 |         10 |                 1 | 10->11                |    2

     17 |         11 |                 2 | 10->11->17         |    3

     18 |         11 |                 2 | 10->11->18         |    4

     12 |         10 |                 1 | 10->12                |    5

     13 |         10 |                1 | 10->13                 |    6

     14 |         13 |                2 | 10->13->14          |    7

     15 |         13 |                2 | 10->13->15          |    8

     16 |         15 |                3 | 10->13->15->16   |    9

(9 rows)

Hope it helps!​

Share this

Relevant Blogs

More Blogs

Switchover/Failover and Session Migration

I have already covered switchover and failover. In the case of failover, the old primary is offline so there are no options for migrating clients from the old primary to...
January 23, 2023