Re: Using Ltree For Hierarchical Structures - Mailing list pgsql-sql

From Don Parris
Subject Re: Using Ltree For Hierarchical Structures
Date
Msg-id CAJ-7yokcbM3Wh2c20xmNwSS8q9MjXpS2Vkh3zohRoaibyhz0WQ@mail.gmail.com
Whole thread Raw
In response to Re: Using Ltree For Hierarchical Structures  (Igor Neyman <ineyman@perceptron.com>)
Responses Re: Using Ltree For Hierarchical Structures
List pgsql-sql
On Tue, Feb 26, 2013 at 2:30 PM, Igor Neyman <ineyman@perceptron.com> wrote:


From: Don Parris [mailto:parrisdc@gmail.com]
Sent: Tuesday, February 26, 2013 1:23 PM
To: Misa Simic
Cc: Igor Neyman; pgsql-sql@postgresql.org
Subject: Re: [SQL] Using Ltree For Hierarchical Structures

Hi Igor,
As Misa points out, my original design used 2 tables - category & line-items.  Either way it's two tables.    The biggest difference lies in the approach to summing line-items by various levels of category - with each branch of the tree having different levels.
I cannot speak to performance, but understanding CTEs has been difficult for me.  Ltree is so much simpler.  I am certain there is a place for CTEs, but why torture myself trying to hash out a CTE when ltree makes the queries so much easier to write?


Don,

To answer this question...
Before I worked (mostly) with Oracle which has "connect by" construct to work with hierarchies.
So, when I switched to Postgres I was happy to find "connect_by" contrib. modul.
And with more recent PG versions, it was just natural transition from contrib. module to recursive CTEs.

Igor


That is helpful to know.  I am unaware of the connect_by module, so will have to look into it.  It is good to know about Oracle's Connect By construct - I wondered how they dealt with that.  If nothing else, I am learning more about CTE's.  This whole thread came about because, while I am normally able to look at example code and translate that into my particular problem, I really got myself in over my head with this category tree.  Most of my queries have been fairly simple so far, but this one stumped me due to my lack of experience.

In the meanwhile, I think understanding CTEs is probably still an important skill, so I am sure I should be familiar with both.  I greatly appreciate everyone's contributions - I am learning a great deal here.  It's one of the reasons why I post my blog stories - to give back a little something of what I've discovered and learned.  Misa gave me an example of CTE code to play around with, and I am going to tackle that in more depth later this week.

Cheers!
Don

--
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
GPG Key ID: F5E179BE

pgsql-sql by date:

Previous
From: Igor Neyman
Date:
Subject: Re: Using Ltree For Hierarchical Structures
Next
From: Misa Simic
Date:
Subject: Re: Using Ltree For Hierarchical Structures