Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wDtd0-003TC4-1F for pgsql-www@arkaria.postgresql.org; Sat, 18 Apr 2026 00:33:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wDtcz-00Bcbz-0I for pgsql-www@arkaria.postgresql.org; Sat, 18 Apr 2026 00:33:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wDppm-00B8RJ-2P for pgsql-www@lists.postgresql.org; Fri, 17 Apr 2026 20:30:38 +0000 Received: from mail-ed1-x52c.google.com ([2a00:1450:4864:20::52c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wDppk-00000001i9k-1k27 for pgsql-www@postgresql.org; Fri, 17 Apr 2026 20:30:38 +0000 Received: by mail-ed1-x52c.google.com with SMTP id 4fb4d7f45d1cf-6729c8f9c55so2832675a12.0 for ; Fri, 17 Apr 2026 13:30:35 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1776457835; cv=none; d=google.com; s=arc-20240605; b=apuWWPSWS3nyrKSBMDYm5OiHsxKYp1qrFTiKGIRIoFACWGDRF1wLWwfCtKM2bkXmFM fG8kMS/KBdOiodyJdJMq7uYxFMrMhIe/Xx7A0Pn8tQOMrZjDHK0Iyyi612raUpAjbjk2 /u81dezg1AQ+ETPAZBVAFf91tJ9ES0Zt4oQbor97cbt0MTuudJDHGqoHJPrU3OkUVhyY eWXijlPpUqnhKmYzzKj53tba1juHFxvEIao7bKBmT9C8HykPZoLetTLjmSsQBvvF2MsZ 2ONF3DVxjH3BZEBykHWPJ4Vtuu6TF7AMkOQ/a/PWgUJnza5Jv2c39s1hhls1cauwoxXv Uy9Q== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=to:subject:message-id:date:from:mime-version:dkim-signature; bh=c2ro0UBAbAa/dlxSdD84z3Zx5Yd6oU2Th1a7ypQquwI=; fh=RR3WQfJneEd2kHqkAYPFc/5BtxyRHuTlziphxi7th6g=; b=XqWe2VleqRo+I4uRA/bjojDNSfg3FjqYhXN8gs4diu2YLLcPkIKvsA96lVF/WkVMj9 90xvum4SB72Htf9QAININoMGGupLs5cprcMQ0mNPKLb7TYXiXaJ1pPevzAznvsnyNLFx NvaBrLblIB8ASeQH9oxwtHAcU7izByLA9KBtmTpjZG3uSa+l0GcADeIBLyQlW6SKOPh6 BLEWwVtxXy7FGgyhXhH7IkMreSm3uw9BBIT5vQo2MYpO9Sz04VQVHRDca5eUq95lDgb3 2RQ4JX1ilyc0lgri0xKVlaNe5K5aIPU09gwfQ7o48mx6MKmUFlXwBYqS400IklEhA6qj bAIQ==; darn=postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1776457835; x=1777062635; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=c2ro0UBAbAa/dlxSdD84z3Zx5Yd6oU2Th1a7ypQquwI=; b=QH0Oevjj80m/XUwxk+CE7JJPc3TJH5uRsQ/iepJMG+9xIIyiy0fC/ZWNK/zJIK01cc PSMmZEnEtF4fqFrmjBGO5zRiDfGg+307ZTPUKM143dnP/2xOuT11GLeUwoB1gGod8EpL 5B1YEdP2aeTD56cqs8fF9lYSg1R4dBfiD0WrhB+Rq2eRgDMHd7cuRfnihigYqKB3EoxP DPjYeIWs8mT6do99ycgcGZkZ2UYenUDTYCd+x8bGe73+ERIP7yV8u0apRz4fRorGi4Ye 8qz9ChHOvye2rp+Eze+y0Th5v/4ALRYb5hwJOwRPC6NDGI04/mLRVgXbu1tBnpcoZ1GO /2+A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1776457835; x=1777062635; h=to:subject:message-id:date:from:mime-version:x-gm-gg :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=c2ro0UBAbAa/dlxSdD84z3Zx5Yd6oU2Th1a7ypQquwI=; b=UlWvNjwX92cqgPJOw7w1Ka2GAm7gpBhWDHuWyncsaa7RCjBzodpuyWW+1GI3ZDcaGi Yhlzyq5XXptwsuaXA/nv2v9cylJbQYZFKMQ48VFGbwn4uXFjRUOMvPj1iBjMY8F9OQeQ EXuROP32xbD7nXuMHGtrn9CXCRILkP4Pw7pFk6FeM8pl+lzWk4ALrZ1HNp8WKQdw9u9t I50wl/0qqhn29PkuDE/rmOfwmloRFn6iUtSLu7etvjyTQDcDJ9T2bj3pouEsPI5BQdiW Wzm83oS13ua6vmRtdXwobwYN7G1VpENYWYPWRfIPaHmZW2+0MdZXVox0aIr55aZFKBYe Gk+g== X-Gm-Message-State: AOJu0Yyj/5/OsXWD90XYBs/FAYIpHz2Y27g2dF0qTu3IoSU+xYEk39U9 BRX5zLNL7oGX38Qfd7O90JMSm68OFNbS8wYImxbs8s0yUAVvXaOSAG+VKCaMiNDy2y22nDwbd+L 4Sjkw40w/GFAxwGEFxsxZpCPjsoDlrvhPUiop X-Gm-Gg: AeBDiesvlqIn3oYT6vBfu4YVGP47BJ/M3eyBDR0IuAwQPhwRnkEGv/nhnoDHADj/Ttq EQJvfQRHUK29ltHTdLaRzFx9TGdPYPOuuTacXB3sDlk2jHiuJwZ1cUVxrcNZB1Nas+xsc1k9mYF hA4MwH2hIFBtTa7FHEku1Tm5Fq9fxRBgA3+j55wgv2dTm8VYDzvuI0i4fbanbIs9Em9UOfe95sQ HKUOgm6a8s28doBLZmrcgFaAwfRb9L8HCoG3KQfd0/ZSHAXTw8nFpxHfw9bOPWw/rNjsPGfDDLQ BuN6mIOwLtCjmi81 X-Received: by 2002:a17:906:7946:b0:ba3:bc3e:d5f3 with SMTP id a640c23a62f3a-ba420066392mr175484866b.5.1776457834286; Fri, 17 Apr 2026 13:30:34 -0700 (PDT) MIME-Version: 1.0 From: "David O'Meara" Date: Fri, 17 Apr 2026 15:30:23 -0500 X-Gm-Features: AQROBzBCcPqg2Uv3UcB9nRMdai9SlSEXHcyHl9z2rCn3v6KWu1sHoY7DpP_4Ozc Message-ID: Subject: Wiki editor request To: pgsql-www@postgresql.org Content-Type: multipart/alternative; boundary="0000000000002bd550064fadd2aa" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002bd550064fadd2aa Content-Type: text/plain; charset="UTF-8" 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) --0000000000002bd550064fadd2aa Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello,

I would like editor access to th= e wiki, my username is=C2=A0nilpunning.

I've f= ound a mistake on=C2=A0https://wiki.postgresql.org/wiki/= Getting_list_of_all_children_from_adjacency_tree and I would like to fi= x.=C2=A0 Under the header PostgreSQL 8.4+ the first example looks like this= :
WITH RECURSIVE tree AS (
  SELECT<=
span class=3D"gmail-w" style=3D"color:rgb(187,187,187)"> id, ARRAY[]::integer[] AS <=
/span>ancestors
  FROM test WHERE parent_id IS NULL

  UNION ALL

  SELECT<=
span class=3D"gmail-w" style=3D"color:rgb(187,187,187)"> test.id, tree.ancestors || test<=
span class=3D"gmail-p">.parent_id
  FROM test, tree<=
/span>
  WHERE test.parent_id =3D tree.id
) SELECT * =
FROM<=
/span> tree WHERE 0 <=
span class=3D"gmail-o" style=3D"color:rgb(102,102,102)">=3D ANY(tree.ancestors)=
;
In particular consid= er this=C2=A0clause: WHERE 0 =3D 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 (<=
/span>
  SELECT<=
span class=3D"gmail-w" style=3D"color:rgb(187,187,187)"> id, ARRAY[]::integer[] AS <=
/span>ancestors
  FROM test WHERE parent_id IS NULL

  UNION ALL

  SELECT<=
span class=3D"gmail-w" style=3D"color:rgb(187,187,187)"> test.id, tree.ancestors || test<=
span class=3D"gmail-p">.parent_id
  FROM test, tree<=
/span>
  WHERE test.parent_id =3D tree.id
) SELECT * =
FROM<=
/span> 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=
=3D# CREATE TABLE test (
testdb(# =C2=A0 =C2=A0 =C2=A0 id =C2=A0 =C2=A0 =
=C2=A0 =C2=A0SERIAL PRIMARY KEY,
testdb(# =C2=A0 =C2=A0 =C2=A0 parent_id=
 INTEGER REFERENCES test(id),
testdb(# =C2=A0 =C2=A0 =C2=A0 x =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 TEXT
testdb(# =C2=A0 );
testdb=3D# insert into test=
(id, parent_id) VALUES (3, NULL), (5,3), (6,3), (9,5);
INSERT 0 4
tes=
tdb=3D# select * from test;
=C2=A0id | parent_id | x
----+-----------=
+---
=C2=A0 3 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
=C2=A0 5 | =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 3 |
=C2=A0 6 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 3 |<=
br>=C2=A0 9 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 5 |
(4 rows)
testdb=3D# WIT=
H RECURSIVE tree AS (
testdb(# =C2=A0 SELECT id, ARRAY[]::integer[] AS a=
ncestors
testdb(# =C2=A0 FROM test WHERE parent_id IS NULL
testdb(#testdb(# =C2=A0 UNION ALL
testdb(#
testdb(# =C2=A0 SELECT test.id, tree.ancestors || test.parent_id
testdb=
(# =C2=A0 FROM test, tree
testdb(# =C2=A0 WHERE test.parent_id =3D tree.id
testdb(# ) SELECT * FROM tree WHERE 0 =
=3D ANY(tree.ancestors);
=C2=A0id | ancestors
----+-----------
(0 =
rows)
testdb=3D# WITH RECURSIVE tree AS (
=C2=A0 SELECT id, ARRAY[]::=
integer[] AS ancestors
=C2=A0 FROM test WHERE parent_id IS NULL
=C2=
=A0 UNION ALL
=C2=A0 SELECT test.id, tree=
.ancestors || test.parent_id
=C2=A0 FROM test, tree
=C2=A0 WHERE test=
.parent_id =3D tree.id
) SELECT * FROM tr=
ee;
=C2=A0id | ancestors
----+-----------
=C2=A0 3 | {}
=C2=A0 =
5 | {3}
=C2=A0 6 | {3}
=C2=A0 9 | {3,5}
(4 rows)


--0000000000002bd550064fadd2aa--