Postgresql query for getting n-level parent-child relation stored in a single table
database, postgresql, recursion, sql
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)
Problem
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.?