Trees in PostgreSQL

depesz has written an other of his great articles. There isn't really much I can add to it, other than it's very nice and doesn't use any PostgreSQL specific elements (like arrays), so it can easily be ported to other DB systems which support triggers.

Personally I only had to implement tree structures once, and then I used an additional field in the table enumerating all the parent nodes in an array and maintained it externally (as opposed to internally with triggers). With a GIN index it is perfect for "give me all the parents and all the children of this node" type of queries.