Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle?
If not, then how can we create a column similar to "LEVEL"?
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;