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

Question

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

Answer 1

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:

• What is the equivalent PostgreSQL syntax to Oracle's CONNECT BY ... START WITH?

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;

Answer 2

Yes, Postgres has support for "LEVEL" like Oracle.

But, as the other answers point out, you must have the tablefunc extension loaded.

If you have admin access to your Postgres database you can load it with this:

CREATE EXTENSION IF NOT EXISTS tablefunc;

For additional info check the docs

https://www.postgresql.org/docs/current/static/tablefunc.html

Here's a real life example of connectby from one of our apps. We are using it to find all the people who report up to a manager through their reporting tree.

SELECT system_user.system_user_id
    , system_user.first_name
    , system_user.last_name
    , team.mgr_id as managers_system_user_id
    , team.level
    , team.hierarchy
    FROM connectby('system_user_manager_rltnp'
                   , 'system_user_id'
                   , 'system_users_managers_id'
                   , 2963049 -- the users system_user_id
                   , 5       -- the max levels of depth
                   , '~')    -- the hierarchy delimiter
    AS team(rpt_id numeric, mgr_id numeric, level int, hierarchy text),
    system_user
    WHERE team.rpt_id = system_user.system_user_id

And it returns results like this. Here you can see the level, and also the whole hierarchy as a string.

"system_user_id","first_name","last_name","managers_system_user_id","level","hierarchy"
"2963049","Debbie","Buswell","",0,"2963049"
"2963045","Linda","Simply","2963049",1,"2963049~2963045"
"2963047","Cindy","Brouillard","2963049",1,"2963049~2963047"
"2963048","Sharon","Burns","2963049",1,"2963049~2963048"
"2963050","Marie-Eve","Casper","2963049",1,"2963049~2963050"
"2963051","Tammy","Cody","2963049",1,"2963049~2963051"