Postgresql query for getting n-level parent-child relation stored in a single table

Question

I have a table denoting parent-child relations. The relations can go n-level deep.

I have created a sample table using the following query:

CREATE SEQUENCE relations_rel_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
CREATE TABLE relations(
    rel_id bigint DEFAULT nextval('relations_rel_id_seq'::regclass) NOT NULL PRIMARY KEY,
    rel_name text,
    rel_display text,
    rel_parent bigint
);

SQLFiddle

I need to query the table and display the parent-child relations hierarchically. I'm still not getting an idea regarding how to query n-level deep using sql query.

For the sqlfiddle eg, the expected hierarchy of output:

rel1
    rel11
        rel111
        rel112
            rel1121
rel2
    rel21
        rel211
        rel212

N.B: The value n, in n-level is unknown.

DB Design:

Is there any better way such a relation can be expressed in the database for easy querying.?

Solution

With Postgres you can use a recursive common table expression:

with recursive rel_tree as (
   select rel_id, rel_name, rel_parent, 1 as level, array[rel_id] as path_info
   from relations 
   where rel_parent is null
   union all
   select c.rel_id, rpad(' ', p.level * 2) || c.rel_name, c.rel_parent, p.level + 1, p.path_info||c.rel_id
   from relations c
     join rel_tree p on c.rel_parent = p.rel_id
)
select rel_id, rel_name
from rel_tree
order by path_info;

SQLFiddle based on your example: http://sqlfiddle.com/#!11/59319/19

(I replaced the spaces for indention with underscores as SQLFiddle doesn't display the spaces correctly)

Comments

[mu is too short]

You're probably looking for WITH RECURSIVE, there's an example over here.

[saji89]

@muistooshort, Thanks for the suggestion. 'll look into it. Do you have any suggestion for alternative db design, that can make it easier for querying and retrieving relations in such cases?

[Ihor Romanchenko]

@saji89 Read this page of the manual. It describes WITH RECURSIVE queries you need.

[Ihor Romanchenko]

@saji89 BTW It is good to mention your RDBMS version. Recursive queries aren't available in PostgreSQL below 8.4.

[mu is too short]

I find the rel_parent structure that you have to be the most natural design for trees; it makes sense and, given WITH RECURSIVE, is easy to work with for all the usual operations. You could look at "nested sets" and "materialized paths" I suppose.

[tscho]

Great answer, exactly what the OP is looking for! But there is a little mistake in the rpad(..) part, changed it to rpad('', p.level * 2, ' '). Again space is replaced with '_' on SQL Fiddle.

[tscho]

Just checked the docs: space is the default fill character (optional 3rd parameter), so I had to use the '_' fill character explicitly on SQLFiddle.

[saji89]

+1, Wow. Postgresql doesn't cease to surprise me. Thankyou.

[mkataja]

For a moment I was at a loss as to how to use a normal with expression together with with recursive. Seems like you're supposed to put the with expression inside the with recursive.

[a_horse_with_no_name]

@mkataja: no, you just add it. with recursive first_cte (...), second_cte (...) select * from second_cte. The recursive keyword is only required once together with the initial with keyword - regardless which CTE is recursive.

[mkataja]

Heh, that makes sense. Somehow I thought the recursive keyword needs to be together with the actual recursive CTE(s).