Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle?

Neha picture Neha · Mar 25, 2014 · Viewed 11.2k times · Source

Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle?
If not, then how can we create a column similar to "LEVEL"?

Answer

Erwin Brandstetter picture Erwin Brandstetter · Mar 25, 2014

Postgres does not have hierarchical queries. No CONNECT BY, therefore also no LEVEL.

The additional module tablefunc provides the function connectby() doing almost the same. See:

Or you can do similar things with a standard recursive CTE and a level column that's incremented with every recursion.
This query in Oracle:

SELECT employee_id, last_name, manager_id, LEVEL
FROM   employees
CONNECT BY PRIOR employee_id = manager_id;

.. can be translated to this recursive CTE in Postgres:

WITH RECURSIVE cte AS (
   SELECT employee_id, last_name, manager_id, 1 AS level
   FROM   employees

   UNION  ALL
   SELECT e.employee_id, e.last_name, e.manager_id, c.level + 1
   FROM   cte c
   JOIN   employees e ON e.manager_id = c.employee_id
   )
SELECT *
FROM   cte;