Postgres: Using WITH RECURSIVE to build list of item and their parents

Question

I have a schema that looks like this:

CREATE TABLE category (
  id SERIAL PRIMARY KEY,
  parent INTEGER REFERENCES category(id) DEFERRABLE,
  name TEXT NOT NULL UNIQUE );
SET CONSTRAINTS ALL DEFERRED;

The rows I have added are:

INSERT INTO category VALUES (1, NULL, 'animal');
INSERT INTO category VALUES (2, 1, 'dog');
INSERT INTO category VALUES (3, 1, 'cat');
INSERT INTO category VALUES (4, 3, 'siamese');
INSERT INTO category VALUES (5, 3, 'persian');
INSERT INTO category VALUES (6, 7, 'scary1');
INSERT INTO category VALUES (7, 6, 'scary2');

I'm trying to figure out how to recursively query this using WITH RECURSIVE to get a result that looks as such:

   ids   |         names      
---------+------------------------
 {1}     | animal
 {2,1}   | dog, animal
 {3,1}   | cat, animal
 {4,3,1} | siamese, cat, animal
 {5,3,1} | persian, cat, animal
 {6,7}   | scary1, scary2
 {7,6}   | scary2, scary1

Where ids is an array containing each parent until the root, and where names is a string with each name separated by commas. I also need to handle the paradox condition without hanging.

Can somebody point me in the right direction?

Show sample data in the table as well.What have you tried so far? — Kaushik Nayak
– Kaushik Nayak, Commented May 8, 2018 at 13:49
@KaushikNayak I have added the row INSERT statements, and I'm not really sure how to approach it honestly. I'm new to this type of query and I'm trying to make sense of the documentation but I'm kind of stuck understanding how I would do this. I realize it's going to require some use of the array functions, but could use an example if somebody knows how to set this up properly. — Nowayz
– Nowayz, Commented May 8, 2018 at 14:03

klin · Accepted Answer · 2018-05-08 18:24:46Z

4

Because of circular dependencies in your data you have to check whether id of the next referenced row does not exist already in the ids array. Use distinct on (id) in the final query and select rows with largest ids array.

with recursive categories as (
    select id, parent, array[id] as ids, name as names
    from category
union all
    select cs.id, c.parent, ids || c.id, concat(names, ', ', name)
    from categories cs
    join category c on cs.parent = c.id
    where c.id <> all(ids)
)
select distinct on (id) ids, names
from categories
order by id, cardinality(ids) desc;

   ids   |        names         
---------+----------------------
 {1}     | animal
 {2,1}   | dog, animal
 {3,1}   | cat, animal
 {4,3,1} | siamese, cat, animal
 {5,3,1} | persian, cat, animal
 {6,7}   | scary1, scary2
 {7,6}   | scary2, scary1
(7 rows)

answered May 8, 2018 at 18:24

klin

123k15 gold badges241 silver badges263 bronze badges

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

1 Comment

Nowayz Over a year ago

Wow! I'm taking it all in now, thank you very much for explaining this

Collectives™ on Stack Overflow

Postgres: Using WITH RECURSIVE to build list of item and their parents

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related