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.
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.
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
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.