We Converted Oracle's CONNECT BY to PostgreSQL WITH RECURSIVE — Then Verified It Row by Row Against Oracle

Getting the SQL to parse isn't the same thing as getting it right. We loaded the same data into both databases and diffed the output.

CONNECT BY is one of the most-used constructs in Oracle PL/SQL estates and one of the least portable. It's Oracle's own syntax for hierarchical queries — org charts, bill-of-materials trees, category hierarchies — and PostgreSQL has no direct equivalent. There's no CONNECT BY keyword to translate into. The entire query has to be re-derived as a recursive common table expression, and Oracle's pseudo-columns (LEVEL, SYS_CONNECT_BY_PATH) have to be reconstructed by hand inside it, because PostgreSQL doesn't have them either.

A typical Oracle hierarchical query:

SELECT empno, ename, mgr, LEVEL,
       SYS_CONNECT_BY_PATH(ename, '/') AS path
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

And its PostgreSQL equivalent:

WITH RECURSIVE org_chart AS (
  SELECT empno, ename, mgr, 1 AS level,
         '/' || ename AS path
  FROM emp
  WHERE mgr IS NULL
  UNION ALL
  SELECT e.empno, e.ename, e.mgr, oc.level + 1,
         oc.path || '/' || e.ename
  FROM emp e
  JOIN org_chart oc ON e.mgr = oc.empno
)
SELECT * FROM org_chart;

That translation is well documented and not hard to get syntactically right. Getting it semantically right — same rows, same evaluation order, same edge cases — is a different problem, and it's the one that actually matters, because a query that parses but returns the wrong tree is worse than a query that honestly refuses to convert at all.

The scope, stated plainly

pgrecon converts a specific, provable subset of CONNECT BY: one table, one PRIOR equality, projections of plain columns, LEVEL, and SYS_CONNECT_BY_PATH with a literal separator. START WITH filters the starting rows, and any WHERE clause outside the hierarchy applies after it, matching Oracle's own evaluation order. NOCYCLE, ORDER SIBLINGS BY, joins inside the hierarchical query, and PRIOR over an expression instead of a plain column all decline by name instead of getting a best-effort guess.

The actual proof

Syntax correctness was never the bar we cared about. So we ran both queries — the original on a live Oracle database, the converted version on the PostgreSQL schema pgrecon produced — against the same loaded data, and compared the output directly:

empno|ename |mgr  |level|path
7839 |KING  |     |1    |/KING
7566 |JONES |7839 |2    |/KING/JONES
7654 |MARTIN|7566 |3    |/KING/JONES/MARTIN
7788 |SCOTT |7566 |3    |/KING/JONES/SCOTT

Identical, row for row: employee number, name, manager, level, and the full path string built by SYS_CONNECT_BY_PATH. Not "looks right." Not "passed a spot check." The same rows, in the same order, read back from two different database engines running two different queries that are supposed to mean the same thing.

Why we don't skip this step

It would be easy to treat "does it parse" as good enough and move on — most tools do, because standing up a live Oracle instance to check every conversion is slower and more annoying than trusting the transpiler. But a recursive CTE that's subtly wrong — an off-by-one on LEVEL, a path built in the wrong direction, a starting condition that includes one row too many — will still run without error. It just won't be your org chart anymore. The only way to know the difference is to ask the original database and compare, which is what we did, and what we'd rather keep doing on every release than assume still holds.

---

The full conversion scope, and the residue rules for what it declines, live at muzzammil242.github.io/pgrecon. For an inventory of every hierarchical query in your own schema — which ones convert and which need a human — the fixed-price assessment covers it: book one at DevCrafter AI. Try it yourself at The github repository.