Recursive Arrays 101

From: David Blomstrom <david(dot)blomstrom(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Recursive Arrays 101
Date: 2015-10-25 15:48:18
Message-ID: CAA54Z0iz0WTrkgF_nQ0onf=kz3JGu0eEqLLgQ7oNO-nOj59mDQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm creating a website focusing on living things (mostly animals). I have
multiple huge MySQL database tables with animal taxons arranged in a
parent-child relationship. I was trying to figure out how I could navigate
to a URL like MySite/life/mammals and display the number of children (i.e.
orders), grandchildren (families), great grandchildren (genera) and great
great grand children (species).

I was then steered towards some sort of MySQL substitute for a full outer
join (which can apparently only be done in Postgre), followed by an
introduction to stored procedures. Pretty complicated stuff.

Then someone told me it's stupid to jump through all those hoops when you
can easily do that sort of thing with Postgre.

So that's my specific goal - to set up my animals website so it can quickly
and efficiently calculate and display things like grandchildren, great
grandparents, the number of children that are extinct, etc.

My database tables look something like this, where Taxon, Parent and
ParentID are the names of the key fields:

Taxon | Parent | ParentID
Animalia | Life | (NULL)
Chordata | Animalia | (NULL)
Animalia | Chordata | 0
Mammalia | Animalia | 1
Carnivora | Mammalia | 2
Felidae | Carnivora | 3
Panthera | Felidae | 2
Panthera-leo | Panthera | 1
Panthera-tirgis | Panthera | 1

Is that table structure sufficient for PostgreSQL to calculate
grand-children, etc., or will I have to modify it? I think the key words
are "hierarchical query" and/or "nested set." There's a popular tutorial
(though I can't find it at the moment) that illustrates the procedure,
which involves creating TWO numerical fields - a process that I think would
be overwhelming when working with over 50,000 taxonomic names.

So that's my question; can I do all this recursive stuff in Postgre with
the table structure posted above, or will I still have to add a second
numerical column (or otherwise my table)?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Blomstrom 2015-10-25 15:50:14 Re: Where do I enter commands?
Previous Message Karsten Hilbert 2015-10-25 15:41:54 Re: Where do I enter commands?