I would like editor access to the wiki, my username is nilpunning.
I've found a mistake on
https://wiki.postgresql.org/wiki/Getting_list_of_all_children_from_adjacency_tree and I would like to fix. Under the header PostgreSQL 8.4+ the first example looks like this:
WITH RECURSIVE tree AS (
SELECT id, ARRAY[]::integer[] AS ancestors
FROM test WHERE parent_id IS NULL
UNION ALL
SELECT test.id, tree.ancestors || test.parent_id
FROM test, tree
WHERE test.parent_id = tree.id
) SELECT * FROM tree WHERE 0 = ANY(tree.ancestors);
In particular consider this clause: WHERE 0 = ANY(tree.ancestors) id | ancestors
------------------------ 3 | {} 5 | {3} 6 | {3} 9 | {3,5}
(4 rows)Since this this is the first example in this section I would just remove the where clause:
WITH RECURSIVE tree AS (
SELECT id, ARRAY[]::integer[] AS ancestors
FROM test WHERE parent_id IS NULL
UNION ALL
SELECT test.id, tree.ancestors || test.parent_id
FROM test, tree
WHERE test.parent_id = tree.id
) SELECT * FROM tree;
This would match the results.
Thanks,
David
root@9c93dad27895:/# psql -U dev -d testdb
psql (12.4 (Debian 12.4-1.pgdg100+1))
Type "help" for help.
testdb=# CREATE TABLE test (
testdb(# id SERIAL PRIMARY KEY,
testdb(# parent_id INTEGER REFERENCES test(id),
testdb(# x TEXT
testdb(# );
testdb=# insert into test(id, parent_id) VALUES (3, NULL), (5,3), (6,3), (9,5);
INSERT 0 4
testdb=# select * from test;
id | parent_id | x
----+-----------+---
3 | |
5 | 3 |
6 | 3 |
9 | 5 |
(4 rows)
testdb=# WITH RECURSIVE tree AS (
testdb(# SELECT id, ARRAY[]::integer[] AS ancestors
testdb(# FROM test WHERE parent_id IS NULL
testdb(#
testdb(# UNION ALL
testdb(#
testdb(# SELECT test.id, tree.ancestors || test.parent_id
testdb(# FROM test, tree
testdb(# WHERE test.parent_id = tree.id
testdb(# ) SELECT * FROM tree WHERE 0 = ANY(tree.ancestors);
id | ancestors
----+-----------
(0 rows)
testdb=# WITH RECURSIVE tree AS (
SELECT id, ARRAY[]::integer[] AS ancestors
FROM test WHERE parent_id IS NULL
UNION ALL
SELECT test.id, tree.ancestors || test.parent_id
FROM test, tree
WHERE test.parent_id = tree.id
) SELECT * FROM tree;
id | ancestors
----+-----------
3 | {}
5 | {3}
6 | {3}
9 | {3,5}
(4 rows)
