PostgreSQL recursive with

Question

I need help with a recursive query. Assuming the following table:

CREATE TEMPORARY TABLE tree (
    id        integer PRIMARY KEY,
    parent_id integer NOT NULL,
    name      varchar(50)
);    

INSERT INTO tree (id, parent_id, name) VALUES (3, 0, 'Peter'), (2,0, 'Thomas'), (5,2, 'David'), (1, 0, 'Rob'), (8, 0, 'Brian');

I can retrieve a list of all people and their children with the following query:

WITH RECURSIVE recursetree(id, parent_id) AS (
    SELECT id, parent_id FROM tree WHERE parent_id = 0
  UNION
    SELECT t.id, t.parent_id
    FROM tree t
    JOIN recursetree rt ON rt.id = t.parent_id
  )
SELECT * FROM recursetree;

How can I list them in order, and also sort the first level items by name? For example, the desired output would be:

id, parent_id, name    
8, 0, "Brian"
3, 0, "Peter"
1, 0; "Rob"
2, 0, "Thomas"
5, 2, "  David"

Thanks,

**EDIT. Please note that adding an ORDER BY won't work: **

WITH RECURSIVE recursetree(id, parent_id, path, name) AS (
    SELECT 
        id, 
        parent_id, 
        array[id] AS path, 
        name 
    FROM tree WHERE parent_id = 0
  UNION ALL
    SELECT t.id, t.parent_id, rt.path || t.id, t.name
    FROM tree t
    JOIN recursetree rt ON rt.id = t.parent_id
  )
SELECT * FROM recursetree ORDER BY path;

The above will retain the parent child relationship (children follow their parents), but applying any other ORDER BY clause (ie: name - like some have suggested) will cause the result to lose it's parent-child relationships.

An ORDER BY wouldn't place children under their parents, only order everything by name. — robdog
– robdog, Commented Jul 22, 2010 at 10:04

Frank Heikens · Accepted Answer · 2010-07-22 10:40:57Z

7

See also this (translated) article about CTE's in PostgreSQL: wiki.phpfreakz.nl

Edit: Try this one, using an array:

WITH RECURSIVE recursetree(id, parent_ids, firstname) AS (
    SELECT id, NULL::int[] || parent_id, name FROM tree WHERE parent_id = 0
  UNION ALL
    SELECT 
    t.id, 
    rt.parent_ids || t.parent_id, 
    name
    FROM tree t
    JOIN recursetree rt ON rt.id = t.parent_id
  )
SELECT * FROM recursetree ORDER BY parent_ids;

edited Jul 22, 2010 at 10:40

answered Jul 22, 2010 at 10:18

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

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

10 Comments

robdog Over a year ago

Thanks for the link. However, the crux of the problem remains - I have to sort by path to retain ordering, but I need to sort top-level items by a field (in this case name).

Frank Heikens Over a year ago

Just use an array in the CTE, a child will always have more elements in the array as it's parents, an ASCending order will do the trick.

araqnid Over a year ago

probably "ORDER BY parent_ids, firstname" to order the top-level items by name as well (which will all have {0} as their parent_ids)

robdog Over a year ago

Frank, I'm not sure what you're suggesting. Are you suggesting storing the path in an array? If so, I still need to sort on that field to retain relationships - which means I won't be able to sort on name. See my edit above.

Frank Heikens Over a year ago

Why wouldn't it be possible to sort on name? It works fine over here. Show us a case that doesn't work. (ps. 0 is an incorrect parent_id, it does not exist. You'd better use NULL when is there is no parent)

|

Bastiaan Wakkie · Accepted Answer · 2022-08-27 14:31:17Z

0

You can add a path to your query and order by it at the end:

WITH RECURSIVE recursetree(id, parent_id,path) AS (
  SELECT id, parent_id,id||'' as path FROM tree WHERE parent_id = 0
UNION
  SELECT t.id, t.parent_id,concat(rt.path,'_',t.id)
  FROM tree t
  JOIN recursetree rt ON rt.id = t.parent_id
) 
SELECT * FROM recursetree
ORDER BY rt.path;

answered Aug 27, 2022 at 14:31

Bastiaan Wakkie

4134 silver badges12 bronze badges

Collectives™ on Stack Overflow

PostgreSQL recursive with

2 Answers 2

10 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related