Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 6FCD2475959 for ; Mon, 27 May 2002 07:55:56 -0400 (EDT) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by postgresql.org (Postfix) with ESMTP id 0BA2B475B43 for ; Mon, 27 May 2002 07:55:50 -0400 (EDT) Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.11.6/8.11.6) with ESMTP id g4RC0Gn01611; Mon, 27 May 2002 15:00:17 +0300 Date: Mon, 27 May 2002 15:00:16 +0300 (EEST) From: Achilleus Mantzios To: Gregory Brauer Cc: Subject: Re: Trees in SQL In-Reply-To: <3CEE818B.9020702@wildbrain.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200205/278 X-Sequence-Number: 7913 On Fri, 24 May 2002, Gregory Brauer wrote: I have exactly the same situation. Some work (argued to have better performance than Celko's implementation) has been done by Miguel Sofer. (see http://www.utdt.edu/~mig/trees.tar.gz) One fast solution would be using pgsql arrays and the contrib/array package. However Oleg's and Theodor's contrib/tree solution looks the most attractive. I will ask my wife today to do a translation of the doc, (hoping russian and yugoslavian are alike :) > > I hope this isn't an overly broad topic that ends up diverging into graph > theory, but I have a tree structure of identical items (analogous to a > filesystem directory tree) that I need to store in Postgres. The > "obvious" design is to give the table that will represent these objects > a field identifying its "parent" that is a relation to the same table. > However, this seems to make many common SQL queries rather difficult. > > What sort of strategies are best for storing tree structures in a > relational database, and how would one structure SQL queries to find, > say, "all of the children anywhere under this node", or to represent > the condition "if this node is a child at any depth under this other > node"? Are there good strategies for preventing cycles? > > I'd appreciate any insights anyone can give. > > Thanks. > > Greg Brauer > greg@wildbrain.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr