PostgreSQL recursive sorting

Question

I have a table named Project in which a parent-child relationship is stored. An entry in the parentprojectid column implies that the respective project instance is a child. The columns of the table are as follows:

projectid, parentprojectid

When I run a SELECT query on this table, the resulting data should consist of every parent followed by all of its children (and the children of its children, if applicable). How do I achieve this?

Here is an example of what the data looks like:

projectid  parentprojid  
proj1      null  
proj11     proj1  
proj12     proj1  
proj121    proj12  
proj2      null  
proj3      null

roman · Accepted Answer · 2013-10-17 12:01:48Z

1

Use recursive common table expression, combine all parents into array and then sort by resulting array:

with recursive cte as (
    select t.projectid, t.parentprojid, array[t.projectid::text] as path
    from Table1 as t
    where t.parentprojid is null
    union all
    select t.projectid, t.parentprojid, c.path || t.projectid::text
    from Table1 as t
        inner join cte as c on c.projectid = t.parentprojid
)
select projectid, parentprojid
from cte
order by path

sql fiddle demo

answered Oct 17, 2013 at 12:01

roman

118k30 gold badges205 silver badges209 bronze badges

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

5 Comments

roman Over a year ago

@a_horse_with_no_name well as far as I see, projectid is already text (varchar actually)

roman Over a year ago

@a_horse_with_no_name yeah, I've tried to find numeric id for some time before realized that this name is id:)

Prmk Over a year ago

What is the purpose behind converting the path column to an array, when the query runs fine even without conversion to array?

roman Over a year ago

well you can convert it to string, but you have to carefully choose separator, like c.path || '->' || t.projectid::text. Imagine you have two parents - 'A' and 'AA' and children 'A' -> 'AA1' and 'AA' -> 'A1'. IF you concatenate it to string without separators, you'll receive two of 'AAA1'. Using array helps not to think about what separator you'll use.

Prmk Over a year ago

Thanks working fine :) Got to learn about the recursive concepts

Collectives™ on Stack Overflow

PostgreSQL recursive sorting

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related