CONNECT BY PRIOR - Hierarchical Queries - Oracle to PostgreSQL Migration

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:

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:

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:

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:

For more information, see Oracle to PostgreSQL Migration.