Skip to content
Advertisement

Recursively getting a root ID of tree in Postgres table

I have a Postgres table users (E_8_User) which contains User_id as a primary key, Boss as a foreign key to the same table (it is not nullable, if some user doesn’t have boss, its Boss attribute = user_id). I need to get their bosses for all users in the table, so I’m trying to write CTE query:

But it doesn’t work: DB is constantly performing query. What am I doing wrong?

Advertisement

Answer

That’s a typical recusive query:

In the recursive query, the trick is to stop recursing when a record is joined with itself (u.boss = c.user_id and u.user_id != c.user_id).

Then, in the outer query, you want to select the record that has the greatest level for each user.

Assuming the following sample data:

user_id | boss
------: | ---:
      1 |    1
      2 |    1
      3 |    2
      4 |    3
      5 |    2
      6 |    6
      7 |    6

The query produces:

user_id | boss
------: | ---:
      1 |    1
      2 |    1
      3 |    1
      4 |    1
      5 |    1
      6 |    6
      7 |    6

Demo on DB Fiddle

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement