Picture of a hierarchy

How to Migrate sys_connect_by_path From Oracle to Postgres

Moving code from Oracle to Postgres can throw up some issues. Especially when migrating keywords specific to Oracle. In this article, I will show you how to migrate sys_connect_by_path from Oracle to Postgres.

Understanding sys_connect_by_path

In Oracle, sys_connect_by_path displays the value of a column in every connected row from a hierarchical query. For example in a query for a family tree, you could use sys_connect_by_path to display all the first names of family members connected by parents.

person_idparent_idfirst_name
1NULLDom
21Andy
32Karen
42Manpreet

A sys_connect_by_path query using the data above could return:

/Dom
/Dom/Andy
/Dom/Andy/Karen
/Dom/Andy/Manpreet

For a more technical explanation of sys_connect_by_path check out the oracle documentation.

Hierarchical Queries in Postgres

To migrate sys_connect_by_path to Postgres, you will first need to understand how recursive queries are set up. Unfortunately, Postgres does not have the recursive query keywords that are available in Oracle. As a result, hierarchical queries are more difficult to set up in Postgres. I recommend checking out my post on recursive queries, before continuing, if you are unsure how they work in Postgres.

Here is an example hierarchical query using the data from the table above:

With recursive example_query as (
  select 
    first_name,
    person_id
  from test_table
  where person_id = 1

  union all

  select 
    child.first_name,
    child.person_id
  from test_table as child
  inner join example_query as parent 
    on child.parent_id = parent.person_id
)
select * from example_query;

Migrating sys_connect_by_path to Postgres

Once you have your recursive query structure, setting up the sys_connect_by_path column is relatively simple. Effectively, what you need is a column in the select statement that will recursively append the value from each connecting row. So, for the example above where we wanted to achieve a path of first names (“/Dom/Andy/Manpreet”), we would do the following:

With recursive example_query as (
  select 
    first_name,
    person_id,
    '/' || first_name as name_path  -- sys_connect_by_path 
  from test_table
  where person_id = 1

  union all

  select 
    child.first_name,
    child.Person_id,
   parent.name_path || '/' || child.first_name as name_path  --  sys_connect_by_path 
  from test_table as child
  inner join example_query as parent 
    on child.parent_id = parent.person_id
)
select * from example_query;

The lines labelled with “– sys_connect_by_path” indicate the code generating the equivalent output in Postgres. The first line generates the initial connected path value – in this case “/Dom”. This is done by using the Postgres concatenate syntax “||” to join “/” and “Dom”. Then, the second line uses Postgres recursion to construct the name path. The select statement below the “union all” is hit multiple times. Each time it is hit, the next name in the path is appended to the parent name path. So, the first time it is hit parent.name_path will equal “/Dom”. The second time, it will equal “/Dom/Andy”. This will go on until the recursive query has reached the end node and then the values will be output. It is as simple as that!

If you wanted to you could change the connecting ‘/’ to any string value you want, or remove it completely. The key is that you reference both the child name and the parent path! The results of the query are shown below, for clarity.

first_name | person_id | name_path
Dom        | 1         | /Dom
Andy       | 2         | /Dom/Andy
Karen      | 3         | /Dom/Andy/Karen
Manpreet   | 4         | /Dom/Andy/Manpreet

Summary

In conclusion, migrating sys_connect_by_path from Oracle to Postgres is fairly straightforward – provided you have an understanding of recursive queries. You need to set up a recursive statement that looks through your table and, during each iteration, concatenates the required value to an overall path string.

Leave a Reply