In Oracle you can use CONNECT BY PRIOR clause of the SELECT statement to build hierarchical queries. PostgreSQL allows you to use Recursive Commom Table Expressions (CTE) to get the same functionality.
One of the simplest use of CONNECT BY is to generate an arbitrary number of rows. For example, the following query generates 5 rows:
Oracle:
SELECT level FROM dual CONNECT BY level <= 5;
Result:
level |
1 |
2 |
3 |
4 |
5 |
In PostgreSQL you can use the following recursive common table expression to get the same result:
PostgreSQL:
WITH RECURSIVE cte AS ( SELECT 1 AS level UNION ALL SELECT level + 1 AS level FROM cte WHERE level <= 4 ) SELECT level FROM cte;
Result:
level |
1 |
2 |
3 |
4 |
5 |
Now let's consider the conversion of hierarchical queries involving multiple tables. Assume with the have the following employees and locations table:
CREATE TABLE employees ( id INT, name VARCHAR(10), mng_id INT ); -- Sample records INSERT INTO employees VALUES(1, 'John', NULL); INSERT INTO employees VALUES(2, 'Paul', 1); INSERT INTO employees VALUES(3, 'Nancy', 1); INSERT INTO employees VALUES(4, 'Sarah', 3); COMMIT; CREATE TABLE locations ( id INT, location VARCHAR(10) ); -- Sample records INSERT INTO locations VALUES (1, 'Home'); INSERT INTO locations VALUES (2, 'Home'); INSERT INTO locations VALUES (4, 'Office'); COMMIT;
In Oracle you can run the following query:
Oracle:
SELECT e.id, name, mng_id, location, level FROM employees e LEFT OUTER JOIN locations l on e.id = l.id START WITH e.id = 1 CONNECT BY PRIOR e.id = mng_id;
Result:
ID | NAME | MNG_ID | LOCATION | LEVEL |
1 | John | Home | 1 | |
2 | Paul | 1 | Home | 2 |
3 | Nancy | 1 | 2 | |
4 | Sarah | 3 | Office | 3 |
Oracle firstly performs the join of two tables and then applies hierarchy recursion for the resulting set.
You can use the following recursive query in PostgreSQL:
PostgreSQL:
-- Note. RECURSIVE keyword has to be specified once at top WITH WITH RECURSIVE base AS ( SELECT e.id, name, mng_id, location FROM employees e LEFT OUTER JOIN locations l on e.id = l.id ), cte AS ( SELECT 1 as level, base.* FROM base WHERE id = 1 UNION ALL SELECT level + 1 as level, base.* FROM base JOIN cte ON base.mng_id = cte.id ) SELECT id, name, mng_id, location, level FROM cte;
Result:
id | name | mng_id | location | level |
1 | John | Home | 1 | |
2 | Paul | 1 | Home | 2 |
3 | Nancy | 1 | 2 | |
4 | Sarah | 3 | Office | 3 |
For more information, see Oracle to PostgreSQL Migration.