[SQL] Modeling trees with Nested Sets and Nested Intervals
I would like to model some hierarchical (tree) data in PostgreSQL. Where can I find high quality Nested Set (or Nested Interval) source code and documentation? I know this question gets asked a lot. To illustrate the point, here is just one thread from each of the last five years: http://archives.postgresql.org/pgsql-sql/2001-08/msg00242.php http://archives.postgresql.org/pgsql-sql/2002-05/msg00270.php http://archives.postgresql.org/pgsql-general/2003-12/msg00247.php http://archives.postgresql.org/pgsql-general/2004-03/msg00804.php http://archives.postgresql.org/pgsql-sql/2005-04/msg00231.php Luckily, no one has asked this question yet in 2006. :-) I've been scouring the Net for a while now, but I hope there are more resources out there that I haven't stumbled onto yet. Here's what I've found so far: * Static Hierarchies and Binary Fractions in PostgreSQL, by Michael Glaesemann http://www.grzm.com/fornow/archives/2004/07/10/static_hierarchies This is the most complete out-of-the-box solution I've found. It uses binary fractions and nested intervals (well, Manfred Koizar says its more of a Materialized Path model). Lots of handholding, documentation, and functions for everything you would want to do to a tree. Limited to 61 nodes in the first branch, plus other limitations. * Modified "m-vgID method", by OpenACS http://cvs.openacs.org/cvs/openacs-4/packages/acs-kernel/sql/postgresql/ Reported to support 2^31 nodes per level, uses bitstring encoding. * m-vgID method, by Miguel Sofer http://www.utdt.edu/~mig/sql-trees/ Uses base 159 encoding (all latin1 chars). * Joe Celko's SQL for Smarties: Advanced SQL Programming, 2nd Edition Highly recommended book. Joe also has a few articles and mailing list posts floating around the web: http://www.dbmsmag.com/9603d06.html http://archives.postgresql.org/pgsql-sql/2001-11/msg4.php http://archives.postgresql.org/pgsql-sql/2003-01/msg00459.php To be clear, I'm not looking for an adjacency model, materialized path model, contrib/ltree, or connect by. Other resources that have been helpful: http://troels.arvin.dk/db/rdbms/links/#hierarchical http://groups.google.com/group/comp.databases.theory/msg/7b772060322df739 Maybe all this would make a good project on pgfoundry. -- Daniel Browning - Kavod Technologies. Random Fortune: To Perl, or not to Perl, that is the kvetching. -- Larry Wall in <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] How to copy data between joined columns?
I have a need to copy/update data from one column to another, based on a join condition. Is this easy to do in pure SQL? I have google'ed without luck for a easy solution (that's not Microsoft specific, that is). For example: postgres=# select xx_thing.date_start,xx_note.created_date from xx_thing join xx_issue using (thing_id) join xx_note using (issue_id) where xx_note.created_date is not null; date_start |created_date +- 2006-03-17 | 1900-01-01 (...many... rows) I need to copy date_start to created_date, for all records that meet a particular where condition. -Bryce Nesbitt -- Visit http://www.obviously.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How to copy data between joined columns?
On Fri, 2006-04-07 at 15:32, Bryce Nesbitt wrote: > I have a need to copy/update data from one column to another, based on a > join condition. Is this easy to do in pure SQL? I have google'ed > without luck for a easy solution (that's not Microsoft specific, that > is). For example: > > postgres=# > select xx_thing.date_start,xx_note.created_date > from xx_thing > join xx_issue using (thing_id) join xx_note using (issue_id) > where xx_note.created_date is not null; > > date_start |created_date > +- > 2006-03-17 | 1900-01-01 > (...many... rows) > > I need to copy date_start to created_date, for all records that meet a > particular where condition. If it's for the same rows in the same table, you're overworking it. update table set field1=field2 where somecondition; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to copy data between joined columns?
Scott Marlowe wrote: > If it's for the same rows in the same table, you're overworking it. > > update table set field1=field2 where somecondition; > If it were for the same rows in the same table, I would not have asked. This is for columns from two tables, whos relationship is only meaningful after a join. -Bryce -- Visit http://www.obviously.com/recycle/";>www.obviously.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] How to copy data between joined columns?
Bryce Nesbitt <[EMAIL PROTECTED]> writes: > If it were for the same rows in the same table, I would not have asked. > This is for columns from two tables, whos relationship is only > meaningful after a join. You have to write out the join condition longhand, eg UPDATE targettable SET targcol = srccol FROM srctable WHERE joincol1 = joincol2; This can be tricky, particularly if there might be more than one source row joining to a given target row. (IIRC, the effective behavior is that any given target row's update will occur against a random one of the possible join partners.) A lot of people prefer to code it using sub-selects. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] How to copy data between joined columns?
Tom Lane wrote: Bryce Nesbitt <[EMAIL PROTECTED]> writes: If it were for the same rows in the same table, I would not have asked. This is for columns from two tables, whos relationship is only meaningful after a join. You have to write out the join condition longhand, eg UPDATE targettable SET targcol = srccol FROM srctable WHERE joincol1 = joincol2; This can be tricky, particularly if there might be more than one source row joining to a given target row. (IIRC, the effective behavior is that any given target row's update will occur against a random one of the possible join partners.) A lot of people prefer to code it using sub-selects. That's great, and will work for me. But I do have the multiple match issue. Can you also give a sub-select example for the archives? Something robust that works against any combination of join partners would be really useful. -- Visit http://www.obviously.com/
