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"?

Ops

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 mthorley answer for details.

Or you can use a standard recursive CTE with 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;

Seeseebeck

Yup. CONNECT BY is cleaner and simpler for the things it can express, but much less flexible than WITH RECURSIVE. – Jenniejennifer

I don't get where you would specify the starting condition, like for level 1 manager_id should be null; but when I add that to the first select it does not work – Summersummerhouse

@Superdooperhero: You are thinking top-down. My query is bottom-up, manager_id IS NULL ends the recursion (iteration really). It's the stopping condition in your manner of speaking. – Seeseebeck

@ErwinBrandstetter How do I change the query to go top-down? – Summersummerhouse

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"

Hid

Answer 3

The functionality using a Connect By, Starts With, and a level indicator that you are familiar with is available if you enable the tablefunc extension in postgres. The syntax is slightly different, but if you understand connect by from oracle you will pick this up in about 90 seconds. It is great and saved my bacon when I transformed an oracle system into a postgres system.

I gave all the detail to a similar question.
Stackoverflow Connect By answer

Coelenterate