Question 282173 in PostgreSQL Question posted in
The official documentation can be found here.

What is the best way to query data from Table if a Table is dependent to itself in Postgresql

I need to fetch data like the below:

select u.id
from public.user u
where u.reporting_id in (select u.id from public.user u
                         where u.reporting_id = ?)

is there any best way of doing same in PostgreSQL

Tags:

2

Answers


  1. 0

    To query data from a self-referencing table in PostgreSQL, you can use a recursive Common Table Expression (CTE) with the WITH RECURSIVE clause. This allows you to traverse the hierarchical structure of the table.

    Here’s an example of how you could query all employees and their hierarchical structure:

    WITH RECURSIVE EmployeeHierarchy AS (
SELECT employee_id, manager_id, 
employee_name
FROM employees
WHERE manager_id IS NULL -- Root level 
 employees (those without a manager)

UNION ALL

SELECT e.employee_id, e.manager_id, 
e.employee_name
FROM employees e
JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

    This query creates a recursive CTE named EmployeeHierarchy that starts with employees who don’t have a manager (manager_id IS NULL) and then recursively joins with employees who have the same manager, continuing the hierarchy. Adjust the column names based on your actual table structure.

    Login or Signup to reply.
  2. 0

    It depends on what you want to achieve.
    For listing users 2 levels down from user 9 for example (users who report to users under user 9), your query (aliasing aside) is not only only correct, it’s the best approach, easiest to optimize by the planner.

    select u.id
from public.user u
where u.reporting_id in(select id from public.user
                        where reporting_id = 9);

    If you actually want the entire structure under user 9, dynamically, regardless of how many levels deep it runs, you’ll need recursion:

    with recursive cte as (
  select reporting_id as super, id as sub
  from public.user where id=9
 union all
  select a.sub as super, b.id as sub
  from cte a left join public.user b on a.sub=b.reporting_id
  where a.sub is not null
) cycle super set is_cycle using path
select * from cte where not is_cycle limit 42;

    Under the hood, it is not actually recursive – it does the job iteratively, re-evaluating the second select and attaching more rows in each round until it reaches a point when it finds nothing or hits a limit.

    It’s always advised that you add that limit clause outside to avoid running out of memory if you do something wrong, as well as cycle detection if your structure isn’t guarded against loops, where a row can link to itself or a sequence of links can result in a closed loop of relations.

    Demo at db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search