public inbox for [email protected]  

help / color / mirror / Atom feed

From: David O'Meara <[email protected]>
To: [email protected]
Subject: Wiki editor request
Date: Fri, 17 Apr 2026 15:30:23 -0500
Message-ID: <CAOpL5sOuWYuEFH-N7YWrQazX1gi35cRxroydc1YvtrYFDWS-sw@mail.gmail.com> (raw)

Hello,

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)
The result ancestors don't include 0:

 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)

view thread (323+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Wiki editor request
  In-Reply-To: <CAOpL5sOuWYuEFH-N7YWrQazX1gi35cRxroydc1YvtrYFDWS-sw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox