From: Gregory Brauer <greg@wildbrain.com>
To: pgsql-sql@postgresql.org
Subject: Trees in SQL
Date: Fri, 24 May 2002 11:08:11 -0700
Message-ID: <3CEE818B.9020702@wildbrain.com> (raw)
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
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: pgsql-sql@postgresql.org
Cc: greg@wildbrain.com
Subject: Re: Trees in SQL
In-Reply-To: <3CEE818B.9020702@wildbrain.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox