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

You're probably looking for WITH RECURSIVE, there's an example over here. — mu is too short
– mu is too short, Commented Feb 2, 2013 at 8:23
@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? — saji89
– saji89, Commented Feb 2, 2013 at 8:44
@saji89 Read this page of the manual. It describes WITH RECURSIVE queries you need. — Ihor Romanchenko
– Ihor Romanchenko, Commented Feb 2, 2013 at 12:56
@saji89 BTW It is good to mention your RDBMS version. Recursive queries aren't available in PostgreSQL below 8.4. — Ihor Romanchenko
– Ihor Romanchenko, Commented Feb 2, 2013 at 12:58
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. — mu is too short
– mu is too short, Commented Feb 2, 2013 at 18:33

user330315 · Accepted Answer · 2013-02-02 16:53:03Z

53

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)

answered Feb 2, 2013 at 16:53

user330315

Sign up to request clarification or add additional context in comments.

6 Comments

tscho Over a year ago

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 Over a year ago

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 Over a year ago

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

mkataja Over a year ago

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.

user330315 Over a year ago

@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.

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related