Junior — Middle

Is it possible to implement recursive queries in PostgreSQL database management system?

sobes.tech AI

Answer from AI

Yes, in PostgreSQL, recursive queries can be implemented using the WITH RECURSIVE construct. This allows executing queries that refer to themselves, for example, to traverse hierarchies or graphs.

An example of a recursive query to get all descendants in a hierarchical structure:

WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE id = 1  -- starting node
  UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

Here, subordinates initially selects the employee with id = 1, and then recursively adds all employees whose manager_id matches the id of already found employees.

Previous Next