Thread: Recursive Arrays 101

Recursive Arrays 101

From
David Blomstrom
Date:
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)?



Re: Recursive Arrays 101

From
Adrian Klaver
Date:
On 10/25/2015 08:48 AM, David Blomstrom wrote:
> 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

I am not entirely following the above. Could you post the actual table
definitions?

>
> 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)?
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Recursive Arrays 101

From
David Blomstrom
Date:
I'm sorry, I don't know exactly what you mean by "definitions." The fields Taxon and Parent are both varchar, with a 50-character limit. ParentID is int(1).

Here's a discussion that describes the table in a little more detail -- http://stackoverflow.com/questions/33248361/hierarchical-query-in-mysql-ii

And this is the discussion where someone suggested I check out PostgreSQL -- http://stackoverflow.com/questions/33313021/displaying-simple-counts-from-stored-procedure

On Sun, Oct 25, 2015 at 10:59 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/25/2015 08:48 AM, David Blomstrom wrote:
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

I am not entirely following the above. Could you post the actual table definitions?



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)?





--
Adrian Klaver
adrian.klaver@aklaver.com



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
Adrian Klaver
Date:
On 10/25/2015 11:12 AM, David Blomstrom wrote:
> I'm sorry, I don't know exactly what you mean by "definitions." The
> fields Taxon and Parent are both varchar, with a 50-character limit.
> ParentID is int(1).

By definition I meant the schema, so from the below:

CREATE TABLE t (
  N INT(6) default None auto_increment,
  Taxon varchar(50) default NULL,
  Parent varchar(25) default NULL,
  NameCommon varchar(50) default NULL,
  Rank smallint(2) default 0
  PRIMARY KEY (N)
) ENGINE=MyISAM

>
> Here's a discussion that describes the table in a little more detail --
> http://stackoverflow.com/questions/33248361/hierarchical-query-in-mysql-ii
>
> And this is the discussion where someone suggested I check out
> PostgreSQL --
> http://stackoverflow.com/questions/33313021/displaying-simple-counts-from-stored-procedure
>

Seems to me it would be easier to use what already exists:

Kingdom, Phylum, Class, Order, Family, Genus, and Species.

So.

Kingdom table <--> Phylum table <--> Class table <-->, on down the line.

Where the tables are linked by Foreign Keys(something not possible with
MyISAM).

See:

http://www.postgresql.org/docs/9.5/static/sql-createtable.html

"REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE
action ] [ ON UPDATE action ] (column constraint)
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn
[, ... ] ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action
] (table constraint)"

--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Recursive Arrays 101

From
Alban Hertroys
Date:
> On 25 Oct 2015, at 19:38, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 10/25/2015 11:12 AM, David Blomstrom wrote:
>> I'm sorry, I don't know exactly what you mean by "definitions." The
>> fields Taxon and Parent are both varchar, with a 50-character limit.
>> ParentID is int(1).
>
> By definition I meant the schema, so from the below:
>
> CREATE TABLE t (
> N INT(6) default None auto_increment,
> Taxon varchar(50) default NULL,
> Parent varchar(25) default NULL,
> NameCommon varchar(50) default NULL,
> Rank smallint(2) default 0
> PRIMARY KEY (N)
> ) ENGINE=MyISAM

That can indeed be solved using a hierarchical query (provided you have a suitable table in PG); something akin to:

WITH RECURSIVE taxons AS (
--  Hierarchical root nodes
    SELECT N AS id, Taxon, Rank, 1 AS level, '' || N AS Path -- A useful addition explained further down
        FROM t
    WHERE ParentID IS NULL

-- Child nodes
    UNION ALL
    SELECT  N AS id, Taxon, Rank, taxons.level +1 AS level, taxons.Path || ':' || N AS Path
    FROM taxons
    JOIN t ON taxons.id = t.ParentID
)
SELECT id, Taxon, Rank, level
FROM taxons
ORDER BY Path
;

The Path-bit looks complicated, but basically that just appends ID's within the same hierarchy such that, when sorted
onthat field, you get the hierarchy in their hierarchical order. What the hierarchy would look like if it were shown as
afile hierarchy with sub-directories expanded, for example. That's pretty much the only viable alternative
(alternativesvary on the column used to create the hierarchy), which is why I added it to the example. 

The fun thing with hierarchical queries is that you can add all kinds of extra information and make it trickle down to
thechild nodes, such as the items that make up the root of the hierarchy (pretty useful for grouping), for example or a
fieldthat calculates a string to prepend for indentation, etc. Or a computation that depends on values in parent items
(Iused this successfully in a bill of materials to calculate absolute quantities by volume, quantities by weight and
costof components in the end product where they were given relative to 1 kg of their parent, for example). 

It's highly flexible and powerful (and standard SQL), but it takes a bit of time to get in the right mindset.

PS. I usually write my hierarchical queries in Oracle, which isn't quite as good at them as Postgres is, but it's what
wehave @work. Hence, I'm not sure I got the syntax 100% correct. We're working on getting PG in for a project upgrade
(replacingRDB on OpenVMS, which will go EOL in <10 years!) - fingers crossed. 

Cheers!

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.



Re: Recursive Arrays 101

From
David Blomstrom
Date:
@ Adrian Klaver: Oh, so you're suggesting I make separate tables for kingdoms, classes and on down to species. I'll research foreign keys and see what I can come up with. I hope I can make separate tables for mammal species, bird species, fish species, etc. There are just so many species - especially fish - the spreadsheets I use to organize them are just about maxed out as it is.

I've been using the Catalogue of Life as a guide, but I'm limited because I can never get their downloads to work. So all I can do is go to their website and copy a bunch of genera and species at a time.

However, I did open up some of the tables I downloaded and was amazed at how apparently amateurish they are. Yet their site works just fine and is fast enough.

@ Alban Hertroys: What does EOL mean? It reminds me of Encyclopedia of Life, which is doing what I was attempting to do years ago.

On Sun, Oct 25, 2015 at 2:07 PM, Alban Hertroys <haramrae@gmail.com> wrote:

> On 25 Oct 2015, at 19:38, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 10/25/2015 11:12 AM, David Blomstrom wrote:
>> I'm sorry, I don't know exactly what you mean by "definitions." The
>> fields Taxon and Parent are both varchar, with a 50-character limit.
>> ParentID is int(1).
>
> By definition I meant the schema, so from the below:
>
> CREATE TABLE t (
> N INT(6) default None auto_increment,
> Taxon varchar(50) default NULL,
> Parent varchar(25) default NULL,
> NameCommon varchar(50) default NULL,
> Rank smallint(2) default 0
> PRIMARY KEY (N)
> ) ENGINE=MyISAM

That can indeed be solved using a hierarchical query (provided you have a suitable table in PG); something akin to:

WITH RECURSIVE taxons AS (
--  Hierarchical root nodes
        SELECT N AS id, Taxon, Rank, 1 AS level, '' || N AS Path -- A useful addition explained further down
        FROM t
        WHERE ParentID IS NULL

-- Child nodes
        UNION ALL
        SELECT  N AS id, Taxon, Rank, taxons.level +1 AS level, taxons.Path || ':' || N AS Path
        FROM taxons
        JOIN t ON taxons.id = t.ParentID
)
SELECT id, Taxon, Rank, level
FROM taxons
ORDER BY Path
;

The Path-bit looks complicated, but basically that just appends ID's within the same hierarchy such that, when sorted on that field, you get the hierarchy in their hierarchical order. What the hierarchy would look like if it were shown as a file hierarchy with sub-directories expanded, for example. That's pretty much the only viable alternative (alternatives vary on the column used to create the hierarchy), which is why I added it to the example.

The fun thing with hierarchical queries is that you can add all kinds of extra information and make it trickle down to the child nodes, such as the items that make up the root of the hierarchy (pretty useful for grouping), for example or a field that calculates a string to prepend for indentation, etc. Or a computation that depends on values in parent items (I used this successfully in a bill of materials to calculate absolute quantities by volume, quantities by weight and cost of components in the end product where they were given relative to 1 kg of their parent, for example).

It's highly flexible and powerful (and standard SQL), but it takes a bit of time to get in the right mindset.

PS. I usually write my hierarchical queries in Oracle, which isn't quite as good at them as Postgres is, but it's what we have @work. Hence, I'm not sure I got the syntax 100% correct. We're working on getting PG in for a project upgrade (replacing RDB on OpenVMS, which will go EOL in <10 years!) - fingers crossed.

Cheers!

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.




--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
John R Pierce
Date:
On 10/25/2015 6:10 PM, David Blomstrom wrote:
> What does EOL mean?

"End of Life"



--
john r pierce, recycling bits in santa cruz



Re: Recursive Arrays 101

From
Adrian Klaver
Date:
On 10/25/2015 06:10 PM, David Blomstrom wrote:
> @ Adrian Klaver: Oh, so you're suggesting I make separate tables for
> kingdoms, classes and on down to species. I'll research foreign keys and
> see what I can come up with. I hope I can make separate tables for
> mammal species, bird species, fish species, etc. There are just so many
> species - especially fish - the spreadsheets I use to organize them are
> just about maxed out as it is.

If you go here:

http://www.catalogueoflife.org/col/browse/classification?71dd35ed0e10acf939d0123cdbf9ce57

that is how you can drill down to a species in the CoL.

It just seems to follow what is already there. No doubt, there are a lot
of species. What is probably more important is that the relationships
have changed over time and can be expected to change more, as genetic
testing for the purpose of taxonomic classification becomes more prevalent.

>
> I've been using the Catalogue of Life as a guide, but I'm limited
> because I can never get their downloads to work. So all I can do is go
> to their website and copy a bunch of genera and species at a time.

Well I downloaded the 2015 snapshot and it turns out it is MySQL
specific. Recently upgraded this computer, will have to see if
MySQL/Mariadb survived the process before I can go any further. It would
be interesting to see how they tackled the relationships.

>
> However, I did open up some of the tables I downloaded and was amazed at
> how apparently amateurish they are. Yet their site works just fine and
> is fast enough.
>
> @ Alban Hertroys: What does EOL mean? It reminds me of Encyclopedia of
> Life, which is doing what I was attempting to do years ago.
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Recursive Arrays 101

From
David Blomstrom
Date:
What was amazed me is the HUGE tables (as in too big to work with or publish online) that, as near as I can remember, have rows like this...

panthera-leo (lion) | Panthera | Felidae | Carnivora | Mammalia | Chordata | Animalia

cramming virtually the entire hierarchy into every single row. Some of my tables have extra columns listing every species family and order, which most people would consider sloppy. But that's tame compared to how they do it.

I've never been able to make their downloads work on my Mac laptop, and the PHP is too complex for me to figure out. Nor have they ever replied to my e-mails. But the websites using their scheme include the Encyclopedia of Life (EOL).

I'm focusing on creating a polished database focusing on vertebrates, along with select invertebrates and plants. After I get that squared away, I'd like to try adding the Catalogue of Life's entire database. The Encyclopedia of Life and WIkipedia are both enormous projects, but there are some amazing gaps in both projects that I hope to fill.

On Sun, Oct 25, 2015 at 8:51 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/25/2015 06:10 PM, David Blomstrom wrote:
@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign keys and
see what I can come up with. I hope I can make separate tables for
mammal species, bird species, fish species, etc. There are just so many
species - especially fish - the spreadsheets I use to organize them are
just about maxed out as it is.

If you go here:

http://www.catalogueoflife.org/col/browse/classification?71dd35ed0e10acf939d0123cdbf9ce57

that is how you can drill down to a species in the CoL.

It just seems to follow what is already there. No doubt, there are a lot of species. What is probably more important is that the relationships have changed over time and can be expected to change more, as genetic testing for the purpose of taxonomic classification becomes more prevalent.


I've been using the Catalogue of Life as a guide, but I'm limited
because I can never get their downloads to work. So all I can do is go
to their website and copy a bunch of genera and species at a time.

Well I downloaded the 2015 snapshot and it turns out it is MySQL specific. Recently upgraded this computer, will have to see if MySQL/Mariadb survived the process before I can go any further. It would be interesting to see how they tackled the relationships.



However, I did open up some of the tables I downloaded and was amazed at
how apparently amateurish they are. Yet their site works just fine and
is fast enough.

@ Alban Hertroys: What does EOL mean? It reminds me of Encyclopedia of
Life, which is doing what I was attempting to do years ago.



--
Adrian Klaver
adrian.klaver@aklaver.com



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
David Blomstrom
Date:
It's also interesting that some entities (e.g. EOL) are now using something called Life Science ID's (or something like that) in lieu of traditional scientific names. It sounds like a cool idea, but some of the LSID's seem awfully big and complex to me. I haven't figured out exactly what the codes mean.

Then again, when I navigate to the Encyclopedia of Life's aardvark page @ http://www.eol.org/pages/327830/overview the code is actually amazingly short.

On Sun, Oct 25, 2015 at 9:04 PM, David Blomstrom <david.blomstrom@gmail.com> wrote:
What was amazed me is the HUGE tables (as in too big to work with or publish online) that, as near as I can remember, have rows like this...

panthera-leo (lion) | Panthera | Felidae | Carnivora | Mammalia | Chordata | Animalia

cramming virtually the entire hierarchy into every single row. Some of my tables have extra columns listing every species family and order, which most people would consider sloppy. But that's tame compared to how they do it.

I've never been able to make their downloads work on my Mac laptop, and the PHP is too complex for me to figure out. Nor have they ever replied to my e-mails. But the websites using their scheme include the Encyclopedia of Life (EOL).

I'm focusing on creating a polished database focusing on vertebrates, along with select invertebrates and plants. After I get that squared away, I'd like to try adding the Catalogue of Life's entire database. The Encyclopedia of Life and WIkipedia are both enormous projects, but there are some amazing gaps in both projects that I hope to fill.

On Sun, Oct 25, 2015 at 8:51 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/25/2015 06:10 PM, David Blomstrom wrote:
@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign keys and
see what I can come up with. I hope I can make separate tables for
mammal species, bird species, fish species, etc. There are just so many
species - especially fish - the spreadsheets I use to organize them are
just about maxed out as it is.

If you go here:

http://www.catalogueoflife.org/col/browse/classification?71dd35ed0e10acf939d0123cdbf9ce57

that is how you can drill down to a species in the CoL.

It just seems to follow what is already there. No doubt, there are a lot of species. What is probably more important is that the relationships have changed over time and can be expected to change more, as genetic testing for the purpose of taxonomic classification becomes more prevalent.


I've been using the Catalogue of Life as a guide, but I'm limited
because I can never get their downloads to work. So all I can do is go
to their website and copy a bunch of genera and species at a time.

Well I downloaded the 2015 snapshot and it turns out it is MySQL specific. Recently upgraded this computer, will have to see if MySQL/Mariadb survived the process before I can go any further. It would be interesting to see how they tackled the relationships.



However, I did open up some of the tables I downloaded and was amazed at
how apparently amateurish they are. Yet their site works just fine and
is fast enough.

@ Alban Hertroys: What does EOL mean? It reminds me of Encyclopedia of
Life, which is doing what I was attempting to do years ago.



--
Adrian Klaver
adrian.klaver@aklaver.com



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
David Blomstrom
Date:
Making it more confusing, I believe there are several different series of numerical ID's. See this page, for example... https://www.wikidata.org/wiki/Q46212

On Sun, Oct 25, 2015 at 9:10 PM, David Blomstrom <david.blomstrom@gmail.com> wrote:
It's also interesting that some entities (e.g. EOL) are now using something called Life Science ID's (or something like that) in lieu of traditional scientific names. It sounds like a cool idea, but some of the LSID's seem awfully big and complex to me. I haven't figured out exactly what the codes mean.

Then again, when I navigate to the Encyclopedia of Life's aardvark page @ http://www.eol.org/pages/327830/overview the code is actually amazingly short.

On Sun, Oct 25, 2015 at 9:04 PM, David Blomstrom <david.blomstrom@gmail.com> wrote:
What was amazed me is the HUGE tables (as in too big to work with or publish online) that, as near as I can remember, have rows like this...

panthera-leo (lion) | Panthera | Felidae | Carnivora | Mammalia | Chordata | Animalia

cramming virtually the entire hierarchy into every single row. Some of my tables have extra columns listing every species family and order, which most people would consider sloppy. But that's tame compared to how they do it.

I've never been able to make their downloads work on my Mac laptop, and the PHP is too complex for me to figure out. Nor have they ever replied to my e-mails. But the websites using their scheme include the Encyclopedia of Life (EOL).

I'm focusing on creating a polished database focusing on vertebrates, along with select invertebrates and plants. After I get that squared away, I'd like to try adding the Catalogue of Life's entire database. The Encyclopedia of Life and WIkipedia are both enormous projects, but there are some amazing gaps in both projects that I hope to fill.

On Sun, Oct 25, 2015 at 8:51 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/25/2015 06:10 PM, David Blomstrom wrote:
@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign keys and
see what I can come up with. I hope I can make separate tables for
mammal species, bird species, fish species, etc. There are just so many
species - especially fish - the spreadsheets I use to organize them are
just about maxed out as it is.

If you go here:

http://www.catalogueoflife.org/col/browse/classification?71dd35ed0e10acf939d0123cdbf9ce57

that is how you can drill down to a species in the CoL.

It just seems to follow what is already there. No doubt, there are a lot of species. What is probably more important is that the relationships have changed over time and can be expected to change more, as genetic testing for the purpose of taxonomic classification becomes more prevalent.


I've been using the Catalogue of Life as a guide, but I'm limited
because I can never get their downloads to work. So all I can do is go
to their website and copy a bunch of genera and species at a time.

Well I downloaded the 2015 snapshot and it turns out it is MySQL specific. Recently upgraded this computer, will have to see if MySQL/Mariadb survived the process before I can go any further. It would be interesting to see how they tackled the relationships.



However, I did open up some of the tables I downloaded and was amazed at
how apparently amateurish they are. Yet their site works just fine and
is fast enough.

@ Alban Hertroys: What does EOL mean? It reminds me of Encyclopedia of
Life, which is doing what I was attempting to do years ago.



--
Adrian Klaver
adrian.klaver@aklaver.com



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
David Blomstrom
Date:
My ultimate goal is to have separate fields for 1) traditional scientific names, 2) LSID's and 3) common names, which are the most confusing thing of all. Some common names are relatively simple and more stable than scientific names - e.g. aardvark and polar bear. The URL MySite/life/polar-bear will always point to the same species, even if scientists reclassified it as a plant or fungus.

But others are more confusing. For example, bison and beaver are both common names and genus names. (Scientists now recognize two separate species of beaver, both in the genus Castor.)

I also have to learn how to use the new search function, Elastic, or whatever it's called. Speaking of which, I just discovered the new Russian and Chinese search engines, Yandex and Baidu. They have some interesting possibilities, too. ;)

On Sun, Oct 25, 2015 at 9:12 PM, David Blomstrom <david.blomstrom@gmail.com> wrote:
Making it more confusing, I believe there are several different series of numerical ID's. See this page, for example... https://www.wikidata.org/wiki/Q46212

On Sun, Oct 25, 2015 at 9:10 PM, David Blomstrom <david.blomstrom@gmail.com> wrote:
It's also interesting that some entities (e.g. EOL) are now using something called Life Science ID's (or something like that) in lieu of traditional scientific names. It sounds like a cool idea, but some of the LSID's seem awfully big and complex to me. I haven't figured out exactly what the codes mean.

Then again, when I navigate to the Encyclopedia of Life's aardvark page @ http://www.eol.org/pages/327830/overview the code is actually amazingly short.

On Sun, Oct 25, 2015 at 9:04 PM, David Blomstrom <david.blomstrom@gmail.com> wrote:
What was amazed me is the HUGE tables (as in too big to work with or publish online) that, as near as I can remember, have rows like this...

panthera-leo (lion) | Panthera | Felidae | Carnivora | Mammalia | Chordata | Animalia

cramming virtually the entire hierarchy into every single row. Some of my tables have extra columns listing every species family and order, which most people would consider sloppy. But that's tame compared to how they do it.

I've never been able to make their downloads work on my Mac laptop, and the PHP is too complex for me to figure out. Nor have they ever replied to my e-mails. But the websites using their scheme include the Encyclopedia of Life (EOL).

I'm focusing on creating a polished database focusing on vertebrates, along with select invertebrates and plants. After I get that squared away, I'd like to try adding the Catalogue of Life's entire database. The Encyclopedia of Life and WIkipedia are both enormous projects, but there are some amazing gaps in both projects that I hope to fill.

On Sun, Oct 25, 2015 at 8:51 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/25/2015 06:10 PM, David Blomstrom wrote:
@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign keys and
see what I can come up with. I hope I can make separate tables for
mammal species, bird species, fish species, etc. There are just so many
species - especially fish - the spreadsheets I use to organize them are
just about maxed out as it is.

If you go here:

http://www.catalogueoflife.org/col/browse/classification?71dd35ed0e10acf939d0123cdbf9ce57

that is how you can drill down to a species in the CoL.

It just seems to follow what is already there. No doubt, there are a lot of species. What is probably more important is that the relationships have changed over time and can be expected to change more, as genetic testing for the purpose of taxonomic classification becomes more prevalent.


I've been using the Catalogue of Life as a guide, but I'm limited
because I can never get their downloads to work. So all I can do is go
to their website and copy a bunch of genera and species at a time.

Well I downloaded the 2015 snapshot and it turns out it is MySQL specific. Recently upgraded this computer, will have to see if MySQL/Mariadb survived the process before I can go any further. It would be interesting to see how they tackled the relationships.



However, I did open up some of the tables I downloaded and was amazed at
how apparently amateurish they are. Yet their site works just fine and
is fast enough.

@ Alban Hertroys: What does EOL mean? It reminds me of Encyclopedia of
Life, which is doing what I was attempting to do years ago.



--
Adrian Klaver
adrian.klaver@aklaver.com



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
Adrian Klaver
Date:
On 10/25/2015 09:10 PM, David Blomstrom wrote:
> It's also interesting that some entities (e.g. EOL) are now using
> something called Life Science ID's (or something like that) in lieu of
> traditional scientific names. It sounds like a cool idea, but some of
> the LSID's seem awfully big and complex to me. I haven't figured out
> exactly what the codes mean.

Aah, the natural key vs surrogate key conversation rears its head.

>
> Then again, when I navigate to the Encyclopedia of Life's aardvark page
> @ http://www.eol.org/pages/327830/overview the code is actually
> amazingly short.
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Recursive Arrays 101

From
David Blomstrom
Date:
LOL - I don't think there are any natural keys here. Traditional scientific names are amazingly flaky. I guess I shouldn't call them flaky; it's just that no one has ever figured out a way do deal with all the complexities of classification. The new LSID's might be more stable - but which LSID does one choose? But it's amazing how many "aliases" are attached to many taxonomic names; utterly bewildering.

On Sun, Oct 25, 2015 at 10:09 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/25/2015 09:10 PM, David Blomstrom wrote:
It's also interesting that some entities (e.g. EOL) are now using
something called Life Science ID's (or something like that) in lieu of
traditional scientific names. It sounds like a cool idea, but some of
the LSID's seem awfully big and complex to me. I haven't figured out
exactly what the codes mean.

Aah, the natural key vs surrogate key conversation rears its head.



Then again, when I navigate to the Encyclopedia of Life's aardvark page
@ http://www.eol.org/pages/327830/overview the code is actually
amazingly short.



--
Adrian Klaver
adrian.klaver@aklaver.com



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
Gavin Flower
Date:
I would suggest that you use int or bigint for primary keys, and have
mapping tables to convert the scientific term to the surrogate primary key.

If the mapping table has additional attributes, like date of change &
reason, then you can also print a history of changes.

Then the relationships between tables will be more isolated from changes
in scientific nomenclature!  Plus if the same animals known by different
scientific names at different times, you can have several mappings to
the same animal.  Also if an organism is moved from one phylum to
another, you can find the organism via either new or old references.
I've heard of cases were one species, is suddenly found to be 2 or
distinct species!


Cheers,
Gavin


On 26/10/15 18:19, David Blomstrom wrote:
> LOL - I don't think there are any natural keys here. Traditional
> scientific names are amazingly flaky. I guess I shouldn't call them
> flaky; it's just that no one has ever figured out a way do deal with
> all the complexities of classification. The new LSID's might be more
> stable - but which LSID does one choose? But it's amazing how many
> "aliases" are attached to many taxonomic names; utterly bewildering.
>
> On Sun, Oct 25, 2015 at 10:09 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 10/25/2015 09:10 PM, David Blomstrom wrote:
>
>         It's also interesting that some entities (e.g. EOL) are now using
>         something called Life Science ID's (or something like that) in
>         lieu of
>         traditional scientific names. It sounds like a cool idea, but
>         some of
>         the LSID's seem awfully big and complex to me. I haven't
>         figured out
>         exactly what the codes mean.
>
>
>     Aah, the natural key vs surrogate key conversation rears its head.
>
>
>
>         Then again, when I navigate to the Encyclopedia of Life's
>         aardvark page
>         @ http://www.eol.org/pages/327830/overview the code is actually
>         amazingly short.
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
>
>
> --
> David Blomstrom
> Writer & Web Designer (Mac, M$ & Linux)
> www.geobop.org <http://www.geobop.org>



Re: Recursive Arrays 101

From
Jim Nasby
Date:
On 10/25/15 8:10 PM, David Blomstrom wrote:
> @ Adrian Klaver: Oh, so you're suggesting I make separate tables for
> kingdoms, classes and on down to species. I'll research foreign keys and
> see what I can come up with. I hope I can make separate tables for
> mammal species, bird species, fish species, etc. There are just so many
> species - especially fish - the spreadsheets I use to organize them are
> just about maxed out as it is.

The suggestion is simply to have 7 tables:

CREATE TABLE kingdom(
   kingdom_id serial PRIMARY KEY
   , kingdom_name text NOT NULL
   , ...
);
CREATE TABLE phylum(
   phylum_id serial PRIMARY KEY
   , kingdom_id int NOT NULL REFERENCES kingdom
   , ...
);
CREATE TABLE class(
...
);

and so-on.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


Re: Recursive Arrays 101

From
Thomas Kellerer
Date:
Alban Hertroys schrieb am 25.10.2015 um 22:07:
> WITH RECURSIVE taxons AS (
> --  Hierarchical root nodes
>     SELECT N AS id, Taxon, Rank, 1 AS level, '' || N AS Path -- A useful addition explained further down
>         FROM t
>     WHERE ParentID IS NULL
>
> -- Child nodes
>     UNION ALL
>     SELECT  N AS id, Taxon, Rank, taxons.level +1 AS level, taxons.Path || ':' || N AS Path
>     FROM taxons
>     JOIN t ON taxons.id = t.ParentID
> )
> SELECT id, Taxon, Rank, level
> FROM taxons
> ORDER BY Path
> ;
>
> The Path-bit looks complicated, but basically that just appends ID's within the same hierarchy such that,
> when sorted on that field, you get the hierarchy in their hierarchical order.

I always wonder whether it's more efficient to aggregate this path using an array rather than a varchar. Mainly because
representingthe numbers as varchars will require more memory than as integer, but then I don't know the overhead of an
arraystructure and whether appending to an array doesn't actually copy it. 

So "array[n] as path" in the root query and "taxons.path||n" in the recursive part.

Any ideas?



Re: Recursive Arrays 101

From
Rob Sargent
Date:
On 10/26/2015 08:43 AM, Jim Nasby wrote:
On 10/25/15 8:10 PM, David Blomstrom wrote:
@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign keys and
see what I can come up with. I hope I can make separate tables for
mammal species, bird species, fish species, etc. There are just so many
species - especially fish - the spreadsheets I use to organize them are
just about maxed out as it is.

The suggestion is simply to have 7 tables:

CREATE TABLE kingdom(
  kingdom_id serial PRIMARY KEY
  , kingdom_name text NOT NULL
  , ...
);
CREATE TABLE phylum(
  phylum_id serial PRIMARY KEY
  , kingdom_id int NOT NULL REFERENCES kingdom
  , ...
);
CREATE TABLE class(
...
);

and so-on.
Seems to me that if life boils down to four attributes one would have a single table with those four attributes on the particular life form.  Now, the four attributes could be ids into definitional tables but I suspect the querying will be done string/name so why complicate the lookups: make the names a foreign key in the defs if necessary.

Personally I think the recursive structure is the way to go.

Re: Recursive Arrays 101

From
Adrian Klaver
Date:
On 10/26/2015 08:12 AM, Rob Sargent wrote:
> On 10/26/2015 08:43 AM, Jim Nasby wrote:
>> On 10/25/15 8:10 PM, David Blomstrom wrote:
>>> @ Adrian Klaver: Oh, so you're suggesting I make separate tables for
>>> kingdoms, classes and on down to species. I'll research foreign keys and
>>> see what I can come up with. I hope I can make separate tables for
>>> mammal species, bird species, fish species, etc. There are just so many
>>> species - especially fish - the spreadsheets I use to organize them are
>>> just about maxed out as it is.
>>
>> The suggestion is simply to have 7 tables:
>>
>> CREATE TABLE kingdom(
>>   kingdom_id serial PRIMARY KEY
>>   , kingdom_name text NOT NULL
>>   , ...
>> );
>> CREATE TABLE phylum(
>>   phylum_id serial PRIMARY KEY
>>   , kingdom_id int NOT NULL REFERENCES kingdom
>>   , ...
>> );
>> CREATE TABLE class(
>> ...
>> );
>>
>> and so-on.
> Seems to me that if life boils down to four attributes one would have a
> single table with those four attributes on the particular life form.

Out of curiosity what are those four attributes? It would have made
memorizing all those organisms a lot easier when I was in school:)


> Now, the four attributes could be ids into definitional tables but I
> suspect the querying will be done string/name so why complicate the
> lookups: make the names a foreign key in the defs if necessary.
>
> Personally I think the recursive structure is the way to go.


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Recursive Arrays 101

From
Rob Sargent
Date:
On 10/26/2015 09:22 AM, Adrian Klaver wrote:
> On 10/26/2015 08:12 AM, Rob Sargent wrote:
>> On 10/26/2015 08:43 AM, Jim Nasby wrote:
>>> On 10/25/15 8:10 PM, David Blomstrom wrote:
>>>> @ Adrian Klaver: Oh, so you're suggesting I make separate tables for
>>>> kingdoms, classes and on down to species. I'll research foreign
>>>> keys and
>>>> see what I can come up with. I hope I can make separate tables for
>>>> mammal species, bird species, fish species, etc. There are just so
>>>> many
>>>> species - especially fish - the spreadsheets I use to organize them
>>>> are
>>>> just about maxed out as it is.
>>>
>>> The suggestion is simply to have 7 tables:
>>>
>>> CREATE TABLE kingdom(
>>>   kingdom_id serial PRIMARY KEY
>>>   , kingdom_name text NOT NULL
>>>   , ...
>>> );
>>> CREATE TABLE phylum(
>>>   phylum_id serial PRIMARY KEY
>>>   , kingdom_id int NOT NULL REFERENCES kingdom
>>>   , ...
>>> );
>>> CREATE TABLE class(
>>> ...
>>> );
>>>
>>> and so-on.
>> Seems to me that if life boils down to four attributes one would have a
>> single table with those four attributes on the particular life form.
>
> Out of curiosity what are those four attributes? It would have made
> memorizing all those organisms a lot easier when I was in school:)
>
kingdom phylum class genus as attributes in species table.  Talk about
your "natural key".  The hibernate boys would love it :)

>> Now, the four attributes could be ids into definitional tables but I
>> suspect the querying will be done string/name so why complicate the
>> lookups: make the names a foreign key in the defs if necessary.
>>
>> Personally I think the recursive structure is the way to go.
>
Jtbc, I'm not advocating this structure but it may suit the OP's usage
patterns.




Re: Recursive Arrays 101

From
Harald Fuchs
Date:
Thomas Kellerer <spam_eater@gmx.net> writes:

> I always wonder whether it's more efficient to aggregate this path
> using an array rather than a varchar. Mainly because representing the
> numbers as varchars will require more memory than as integer, but then
> I don't know the overhead of an array structure and whether appending
> to an array doesn't actually copy it.

If you go that direction, you're not far away from the ltree extension
(which might also be a solution for the OP's problem).

Re: Recursive Arrays 101

From
Adrian Klaver
Date:
On 10/26/2015 08:32 AM, Rob Sargent wrote:
> On 10/26/2015 09:22 AM, Adrian Klaver wrote:
>> On 10/26/2015 08:12 AM, Rob Sargent wrote:
>>> On 10/26/2015 08:43 AM, Jim Nasby wrote:
>>>> On 10/25/15 8:10 PM, David Blomstrom wrote:
>>>>> @ Adrian Klaver: Oh, so you're suggesting I make separate tables for
>>>>> kingdoms, classes and on down to species. I'll research foreign
>>>>> keys and
>>>>> see what I can come up with. I hope I can make separate tables for
>>>>> mammal species, bird species, fish species, etc. There are just so
>>>>> many
>>>>> species - especially fish - the spreadsheets I use to organize them
>>>>> are
>>>>> just about maxed out as it is.
>>>>
>>>> The suggestion is simply to have 7 tables:
>>>>
>>>> CREATE TABLE kingdom(
>>>>   kingdom_id serial PRIMARY KEY
>>>>   , kingdom_name text NOT NULL
>>>>   , ...
>>>> );
>>>> CREATE TABLE phylum(
>>>>   phylum_id serial PRIMARY KEY
>>>>   , kingdom_id int NOT NULL REFERENCES kingdom
>>>>   , ...
>>>> );
>>>> CREATE TABLE class(
>>>> ...
>>>> );
>>>>
>>>> and so-on.
>>> Seems to me that if life boils down to four attributes one would have a
>>> single table with those four attributes on the particular life form.
>>
>> Out of curiosity what are those four attributes? It would have made
>> memorizing all those organisms a lot easier when I was in school:)
>>
> kingdom phylum class genus as attributes in species table.  Talk about
> your "natural key".  The hibernate boys would love it :)

Well in this classification system it would need to be:

kingdom phylum class order family genus

What makes it complicated is that these are just the slots. How
organisms are slotted depends on attributes and there are a lot of them.
This means there is a constant rearrangement in the slotting.

>
>>> Now, the four attributes could be ids into definitional tables but I
>>> suspect the querying will be done string/name so why complicate the
>>> lookups: make the names a foreign key in the defs if necessary.
>>>
>>> Personally I think the recursive structure is the way to go.
>>
> Jtbc, I'm not advocating this structure but it may suit the OP's usage
> patterns.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Recursive Arrays 101

From
Rob Sargent
Date:
On 10/26/2015 11:14 AM, Adrian Klaver wrote:
> On 10/26/2015 08:32 AM, Rob Sargent wrote:
>> On 10/26/2015 09:22 AM, Adrian Klaver wrote:
>>> On 10/26/2015 08:12 AM, Rob Sargent wrote:
>>>> On 10/26/2015 08:43 AM, Jim Nasby wrote:
>>>>> On 10/25/15 8:10 PM, David Blomstrom wrote:
>>>>>> @ Adrian Klaver: Oh, so you're suggesting I make separate tables for
>>>>>> kingdoms, classes and on down to species. I'll research foreign
>>>>>> keys and
>>>>>> see what I can come up with. I hope I can make separate tables for
>>>>>> mammal species, bird species, fish species, etc. There are just so
>>>>>> many
>>>>>> species - especially fish - the spreadsheets I use to organize them
>>>>>> are
>>>>>> just about maxed out as it is.
>>>>>
>>>>> The suggestion is simply to have 7 tables:
>>>>>
>>>>> CREATE TABLE kingdom(
>>>>>   kingdom_id serial PRIMARY KEY
>>>>>   , kingdom_name text NOT NULL
>>>>>   , ...
>>>>> );
>>>>> CREATE TABLE phylum(
>>>>>   phylum_id serial PRIMARY KEY
>>>>>   , kingdom_id int NOT NULL REFERENCES kingdom
>>>>>   , ...
>>>>> );
>>>>> CREATE TABLE class(
>>>>> ...
>>>>> );
>>>>>
>>>>> and so-on.
>>>> Seems to me that if life boils down to four attributes one would
>>>> have a
>>>> single table with those four attributes on the particular life form.
>>>
>>> Out of curiosity what are those four attributes? It would have made
>>> memorizing all those organisms a lot easier when I was in school:)
>>>
>> kingdom phylum class genus as attributes in species table.  Talk about
>> your "natural key".  The hibernate boys would love it :)
>
> Well in this classification system it would need to be:
>
> kingdom phylum class order family genus
Sorry, wasn't tracking carefully: 6 attributes
>
> What makes it complicated is that these are just the slots. How
> organisms are slotted depends on attributes and there are a lot of
> them. This means there is a constant rearrangement in the slotting.
>
But at the end of the day, is it not the intent to have those six filled
per species. Is your point that maintenance would be problematic?
Agreed.  Certainly not just a single pointer redirect in a recursive
structure.  All depends on OPs usage patterns.  I personally love 'with
recursion' but it's more complicated than for example
     select count(*) from species where class = '<some class name>'
if, and only if, all 6 attributes are always there.  Which highlights
your caveat "In this classification system".

>>>> Now, the four attributes could be ids into definitional tables but I
>>>> suspect the querying will be done string/name so why complicate the
>>>> lookups: make the names a foreign key in the defs if necessary.
>>>>
>>>> Personally I think the recursive structure is the way to go.
>>>
>> Jtbc, I'm not advocating this structure but it may suit the OP's usage
>> patterns.
>>
>>
>
>



Re: Recursive Arrays 101

From
Adrian Klaver
Date:
On 10/26/2015 10:33 AM, Rob Sargent wrote:
> On 10/26/2015 11:14 AM, Adrian Klaver wrote:
>> On 10/26/2015 08:32 AM, Rob Sargent wrote:
>>> On 10/26/2015 09:22 AM, Adrian Klaver wrote:
>>>> On 10/26/2015 08:12 AM, Rob Sargent wrote:
>>>>> On 10/26/2015 08:43 AM, Jim Nasby wrote:
>>>>>> On 10/25/15 8:10 PM, David Blomstrom wrote:
>>>>>>> @ Adrian Klaver: Oh, so you're suggesting I make separate tables for
>>>>>>> kingdoms, classes and on down to species. I'll research foreign
>>>>>>> keys and
>>>>>>> see what I can come up with. I hope I can make separate tables for
>>>>>>> mammal species, bird species, fish species, etc. There are just so
>>>>>>> many
>>>>>>> species - especially fish - the spreadsheets I use to organize them
>>>>>>> are
>>>>>>> just about maxed out as it is.
>>>>>>
>>>>>> The suggestion is simply to have 7 tables:
>>>>>>
>>>>>> CREATE TABLE kingdom(
>>>>>>   kingdom_id serial PRIMARY KEY
>>>>>>   , kingdom_name text NOT NULL
>>>>>>   , ...
>>>>>> );
>>>>>> CREATE TABLE phylum(
>>>>>>   phylum_id serial PRIMARY KEY
>>>>>>   , kingdom_id int NOT NULL REFERENCES kingdom
>>>>>>   , ...
>>>>>> );
>>>>>> CREATE TABLE class(
>>>>>> ...
>>>>>> );
>>>>>>
>>>>>> and so-on.
>>>>> Seems to me that if life boils down to four attributes one would
>>>>> have a
>>>>> single table with those four attributes on the particular life form.
>>>>
>>>> Out of curiosity what are those four attributes? It would have made
>>>> memorizing all those organisms a lot easier when I was in school:)
>>>>
>>> kingdom phylum class genus as attributes in species table.  Talk about
>>> your "natural key".  The hibernate boys would love it :)
>>
>> Well in this classification system it would need to be:
>>
>> kingdom phylum class order family genus
> Sorry, wasn't tracking carefully: 6 attributes
>>
>> What makes it complicated is that these are just the slots. How
>> organisms are slotted depends on attributes and there are a lot of
>> them. This means there is a constant rearrangement in the slotting.
>>
> But at the end of the day, is it not the intent to have those six filled
> per species. Is your point that maintenance would be problematic?
> Agreed.  Certainly not just a single pointer redirect in a recursive
> structure.  All depends on OPs usage patterns.  I personally love 'with
> recursion' but it's more complicated than for example
>      select count(*) from species where class = '<some class name>'
> if, and only if, all 6 attributes are always there.  Which highlights
> your caveat "In this classification system".

This is the current system. If you want to be historically complete then
you have to take into account the ways things where classified before.
Granted this is running in the crawl, walk , run sequence but it cannot
be entirely ignored. Then there  are the more detailed versions of the
above:

http://www.itis.gov/servlet/SingleRpt/SingleRpt?search_topic=TSN&search_value=584927

It comes done to what view of taxonomy you want to support.

>
>>>>> Now, the four attributes could be ids into definitional tables but I
>>>>> suspect the querying will be done string/name so why complicate the
>>>>> lookups: make the names a foreign key in the defs if necessary.
>>>>>
>>>>> Personally I think the recursive structure is the way to go.
>>>>
>>> Jtbc, I'm not advocating this structure but it may suit the OP's usage
>>> patterns.
>>>
>>>
>>
>>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Recursive Arrays 101

From
David Blomstrom
Date:
Sorry for the late response. I don't have Internet access at home, so I only post from the library or a WiFi cafe.

Anyway, where do I begin?

Regarding my "usage patterns," I use spreadsheets (Apple's Numbers program) to organize data. I then save it as a CSV file and import it into a database table. It would be very hard to break with that tradition, because I don't know of any other way to organize my data.

On the other hand, I have a column (Rank) that identifies different taxonomic levels (kingdom, class, etc.). So I can easily sort a table into specific taxonomic levels and save one level at a time for a database table.

There is one problem, though. I can easily put all the vertebrate orders and even families into a table. But genera might be harder, and species probably won't work; there are simply too many. My spreadsheet program is almost overwhelmed by fish species alone. The only solution would be if I could import Mammals.csv, then import Birds.csv, Reptiles.csv, etc. But that might be kind of tedious, especially if I have to make multiple updates.

As for "attributes," I'll post my table's schema, with a description, next.

On Mon, Oct 26, 2015 at 10:44 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/26/2015 10:33 AM, Rob Sargent wrote:
On 10/26/2015 11:14 AM, Adrian Klaver wrote:
On 10/26/2015 08:32 AM, Rob Sargent wrote:
On 10/26/2015 09:22 AM, Adrian Klaver wrote:
On 10/26/2015 08:12 AM, Rob Sargent wrote:
On 10/26/2015 08:43 AM, Jim Nasby wrote:
On 10/25/15 8:10 PM, David Blomstrom wrote:
@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign
keys and
see what I can come up with. I hope I can make separate tables for
mammal species, bird species, fish species, etc. There are just so
many
species - especially fish - the spreadsheets I use to organize them
are
just about maxed out as it is.

The suggestion is simply to have 7 tables:

CREATE TABLE kingdom(
  kingdom_id serial PRIMARY KEY
  , kingdom_name text NOT NULL
  , ...
);
CREATE TABLE phylum(
  phylum_id serial PRIMARY KEY
  , kingdom_id int NOT NULL REFERENCES kingdom
  , ...
);
CREATE TABLE class(
...
);

and so-on.
Seems to me that if life boils down to four attributes one would
have a
single table with those four attributes on the particular life form.

Out of curiosity what are those four attributes? It would have made
memorizing all those organisms a lot easier when I was in school:)

kingdom phylum class genus as attributes in species table.  Talk about
your "natural key".  The hibernate boys would love it :)

Well in this classification system it would need to be:

kingdom phylum class order family genus
Sorry, wasn't tracking carefully: 6 attributes

What makes it complicated is that these are just the slots. How
organisms are slotted depends on attributes and there are a lot of
them. This means there is a constant rearrangement in the slotting.

But at the end of the day, is it not the intent to have those six filled
per species. Is your point that maintenance would be problematic?
Agreed.  Certainly not just a single pointer redirect in a recursive
structure.  All depends on OPs usage patterns.  I personally love 'with
recursion' but it's more complicated than for example
     select count(*) from species where class = '<some class name>'
if, and only if, all 6 attributes are always there.  Which highlights
your caveat "In this classification system".

This is the current system. If you want to be historically complete then you have to take into account the ways things where classified before. Granted this is running in the crawl, walk , run sequence but it cannot be entirely ignored. Then there  are the more detailed versions of the above:

http://www.itis.gov/servlet/SingleRpt/SingleRpt?search_topic=TSN&search_value=584927

It comes done to what view of taxonomy you want to support.


Now, the four attributes could be ids into definitional tables but I
suspect the querying will be done string/name so why complicate the
lookups: make the names a foreign key in the defs if necessary.

Personally I think the recursive structure is the way to go.

Jtbc, I'm not advocating this structure but it may suit the OP's usage
patterns.







--
Adrian Klaver
adrian.klaver@aklaver.com



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
Rob Sargent
Date:
On 10/26/2015 02:29 PM, David Blomstrom wrote:
> Sorry for the late response. I don't have Internet access at home, so
> I only post from the library or a WiFi cafe.
>
> Anyway, where do I begin?
>
> Regarding my "usage patterns," I use spreadsheets (Apple's Numbers
> program) to organize data. I then save it as a CSV file and import it
> into a database table. It would be very hard to break with that
> tradition, because I don't know of any other way to organize my data.
>
> On the other hand, I have a column (Rank) that identifies different
> taxonomic levels (kingdom, class, etc.). So I can easily sort a table
> into specific taxonomic levels and save one level at a time for a
> database table.
>
> There is one problem, though. I can easily put all the vertebrate
> orders and even families into a table. But genera might be harder, and
> species probably won't work; there are simply too many. My spreadsheet
> program is almost overwhelmed by fish species alone. The only solution
> would be if I could import Mammals.csv, then import Birds.csv,
> Reptiles.csv, etc. But that might be kind of tedious, especially if I
> have to make multiple updates.
>
Yes I suspect you spreadsheet will be limited in rows, but of course you
can send all the spreadsheets to a single table in the database. If
that's what you want.  You don't have to, but you see mention of tables
millions of records routinely.  On the other hand, if performance
becomes an issue with the single table approach you might want to look
at "partitioning".  But I would be surprised if you had to go there.

What is your data source?  How much hand-entry are you doing? There are
tools which (seriously) upgrade the basic 'COPY into <table>' command.

> As for "attributes," I'll post my table's schema, with a description,
> next.
>



Re: Recursive Arrays 101

From
David Blomstrom
Date:
I've created my first table in postgreSQL. I'd like to ask 1) if you see any errors, 2) do you have any suggestions for improving it, and 3) can you give me the code I need to paste into the shell (or whatever you call the command-line tool) to recreate it?

This is what the table's schema looks like in MySQL...

N - int(6) [Primary Key]
Taxon - varchar(50) [Unique Key]
Parent - varchar(50) [Index Key]
ParentID - tinyint(1) [Index Key]
Slug - varchar(50) [Index Key]
NameCommon - varchar(50)
Plural - varchar(50)
Extinct - tinyint(1)
Rank - tinyint(2)
Key - tinyint(1)

The table type is MyIsam, collation is latin1_general_ci

Slug, NameCommon and Plural are NULL.

All of my tables have a default first column named N or ID, which is simply a numerical key that begins with 1. It's always designated the primary key.

All the other columns in this table can be divided into two categories, text (varchar) and numerical (tinyint).

The values in the columns Taxon and Slug serve as URL's, so they can have no spaces, apostrophes, accents, etc. (Taxon handles scientific names, Slug common names, if any.) So a row focusing on the Steller's jay would have values like these:

NameCommmon - Steller&#8217;s jay
Plural - Steller&#8217;s jays
Taxon - Cyanocitta-stelleri
Slug - stellers-jay
Parent - Cyanocitta

The column ParentID - which I want to use for hierarchical relationships - has values ranging from 1 for Mammalia (the first row) to 5 for the species level. The column Extinct has the value 1 (not extinct) or 2, 3 or 4 for various categories of extinct taxons.

The column Rank has the value 25 for the first row (class Mammalia), 35 for each order (e.g. Carnivora), 45 for each family, 55 for each genus and 65 for each species. The value for Key is 1 (for every row), designating it a tetrapod. The bird, reptile and amphibian tables have the same key value, while fish, invertebrates and plants have their own unique keys.

I have Unique keys on N and Taxon, Index keys (not unique) on Parent, ParentID and Slug.

My PostgreSQL table is in a database named GeoZoo. When I go into pgAdmin3 > SQLPane, it looks like this:

CREATE TABLE public.gz_life_mammals
(
  "N" integer NOT NULL,
  "Taxon" character varying(50) NOT NULL,
  "Parent" character varying(50) NOT NULL,
  "ParentID" smallint NOT NULL,
  "Slug" character varying(50),
  "NameCommon" character varying(50),
  "Plural" character varying(50),
  "Extinct" smallint NOT NULL,
  "Rank" smallint NOT NULL,
  "Key" smallint NOT NULL,
  CONSTRAINT "Primary Key" PRIMARY KEY ("N"),
  CONSTRAINT "Unique Key" UNIQUE ("Taxon")
[I haven't added any non-unique keys yet.]
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.gz_life_mammals
  OWNER TO postgres;

I should also mention that Taxon is the column I use to UNION or JOIN this table with other tables.

P.S. If I decide to organize things by taxonomic levels (e.g. kingdom, class, etc.), then it should be easy to rename the table, delete a few columns, and refill it with data associated with a particular class.



On Mon, Oct 26, 2015 at 1:29 PM, David Blomstrom <david.blomstrom@gmail.com> wrote:
Sorry for the late response. I don't have Internet access at home, so I only post from the library or a WiFi cafe.

Anyway, where do I begin?

Regarding my "usage patterns," I use spreadsheets (Apple's Numbers program) to organize data. I then save it as a CSV file and import it into a database table. It would be very hard to break with that tradition, because I don't know of any other way to organize my data.

On the other hand, I have a column (Rank) that identifies different taxonomic levels (kingdom, class, etc.). So I can easily sort a table into specific taxonomic levels and save one level at a time for a database table.

There is one problem, though. I can easily put all the vertebrate orders and even families into a table. But genera might be harder, and species probably won't work; there are simply too many. My spreadsheet program is almost overwhelmed by fish species alone. The only solution would be if I could import Mammals.csv, then import Birds.csv, Reptiles.csv, etc. But that might be kind of tedious, especially if I have to make multiple updates.

As for "attributes," I'll post my table's schema, with a description, next.

On Mon, Oct 26, 2015 at 10:44 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/26/2015 10:33 AM, Rob Sargent wrote:
On 10/26/2015 11:14 AM, Adrian Klaver wrote:
On 10/26/2015 08:32 AM, Rob Sargent wrote:
On 10/26/2015 09:22 AM, Adrian Klaver wrote:
On 10/26/2015 08:12 AM, Rob Sargent wrote:
On 10/26/2015 08:43 AM, Jim Nasby wrote:
On 10/25/15 8:10 PM, David Blomstrom wrote:
@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign
keys and
see what I can come up with. I hope I can make separate tables for
mammal species, bird species, fish species, etc. There are just so
many
species - especially fish - the spreadsheets I use to organize them
are
just about maxed out as it is.

The suggestion is simply to have 7 tables:

CREATE TABLE kingdom(
  kingdom_id serial PRIMARY KEY
  , kingdom_name text NOT NULL
  , ...
);
CREATE TABLE phylum(
  phylum_id serial PRIMARY KEY
  , kingdom_id int NOT NULL REFERENCES kingdom
  , ...
);
CREATE TABLE class(
...
);

and so-on.
Seems to me that if life boils down to four attributes one would
have a
single table with those four attributes on the particular life form.

Out of curiosity what are those four attributes? It would have made
memorizing all those organisms a lot easier when I was in school:)

kingdom phylum class genus as attributes in species table.  Talk about
your "natural key".  The hibernate boys would love it :)

Well in this classification system it would need to be:

kingdom phylum class order family genus
Sorry, wasn't tracking carefully: 6 attributes

What makes it complicated is that these are just the slots. How
organisms are slotted depends on attributes and there are a lot of
them. This means there is a constant rearrangement in the slotting.

But at the end of the day, is it not the intent to have those six filled
per species. Is your point that maintenance would be problematic?
Agreed.  Certainly not just a single pointer redirect in a recursive
structure.  All depends on OPs usage patterns.  I personally love 'with
recursion' but it's more complicated than for example
     select count(*) from species where class = '<some class name>'
if, and only if, all 6 attributes are always there.  Which highlights
your caveat "In this classification system".

This is the current system. If you want to be historically complete then you have to take into account the ways things where classified before. Granted this is running in the crawl, walk , run sequence but it cannot be entirely ignored. Then there  are the more detailed versions of the above:

http://www.itis.gov/servlet/SingleRpt/SingleRpt?search_topic=TSN&search_value=584927

It comes done to what view of taxonomy you want to support.


Now, the four attributes could be ids into definitional tables but I
suspect the querying will be done string/name so why complicate the
lookups: make the names a foreign key in the defs if necessary.

Personally I think the recursive structure is the way to go.

Jtbc, I'm not advocating this structure but it may suit the OP's usage
patterns.







--
Adrian Klaver
adrian.klaver@aklaver.com



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
David Blomstrom
Date:
I'm focusing primarily on vertebrates at the moment, which have a total of (I think) about 60,000-70,000 rows for all taxons (species, families, etc.). My goal is to create a customized database that does a really good job of handling vertebrates first, manually adding a few key invertebrates and plants as needed.

I couldn't possibly repeat the process with invertebrates or plants, which are simply overwhelming. So, if I ever figure out the Catalogue of Life's database, then I'm simply going to modify its tables so they work with my system. My vertebrates database will override their vertebrate rows (except for any extra information they have to offer).

As for "hand-entry," I do almost all my work in spreadsheets. I spent a day or two copying scientific names from the Catalogue of Life into my spreadsheet. Common names and slugs (common names in a URL format) is a project that will probably take years. I might type a scientific name or common name into Google and see where it leads me. If a certain scientific name is associated with the common name "yellow birch," then its slug becomes yellow-birch. If two or more species are called yellow birch, then I enter yellow-birch in a different table ("Floaters"), which leads to a disambiguation page.

For organisms with two or more popular common names - well, I haven't really figured that out yet. I'll probably have to make an extra table for additional names. Catalogue of Life has common names in its database, but they all have upper case first letters - like American Beaver. That works fine for a page title but in regular text I need to make beaver lowercase without changing American. So I'm just starting from square one and recreating all the common names from scratch.

It gets still more complicated when you get into "specialist names." ;) But the system I've set up so far seems to be working pretty nicely.

On Mon, Oct 26, 2015 at 1:41 PM, Rob Sargent <robjsargent@gmail.com> wrote:
On 10/26/2015 02:29 PM, David Blomstrom wrote:
Sorry for the late response. I don't have Internet access at home, so I only post from the library or a WiFi cafe.

Anyway, where do I begin?

Regarding my "usage patterns," I use spreadsheets (Apple's Numbers program) to organize data. I then save it as a CSV file and import it into a database table. It would be very hard to break with that tradition, because I don't know of any other way to organize my data.

On the other hand, I have a column (Rank) that identifies different taxonomic levels (kingdom, class, etc.). So I can easily sort a table into specific taxonomic levels and save one level at a time for a database table.

There is one problem, though. I can easily put all the vertebrate orders and even families into a table. But genera might be harder, and species probably won't work; there are simply too many. My spreadsheet program is almost overwhelmed by fish species alone. The only solution would be if I could import Mammals.csv, then import Birds.csv, Reptiles.csv, etc. But that might be kind of tedious, especially if I have to make multiple updates.

Yes I suspect you spreadsheet will be limited in rows, but of course you can send all the spreadsheets to a single table in the database. If that's what you want.  You don't have to, but you see mention of tables millions of records routinely.  On the other hand, if performance becomes an issue with the single table approach you might want to look at "partitioning".  But I would be surprised if you had to go there.

What is your data source?  How much hand-entry are you doing? There are tools which (seriously) upgrade the basic 'COPY into <table>' command.


As for "attributes," I'll post my table's schema, with a description, next.





--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
Rob Sargent
Date:
On 10/26/2015 02:51 PM, David Blomstrom wrote:
I'm focusing primarily on vertebrates at the moment, which have a total of (I think) about 60,000-70,000 rows for all taxons (species, families, etc.). My goal is to create a customized database that does a really good job of handling vertebrates first, manually adding a few key invertebrates and plants as needed.

I couldn't possibly repeat the process with invertebrates or plants, which are simply overwhelming. So, if I ever figure out the Catalogue of Life's database, then I'm simply going to modify its tables so they work with my system. My vertebrates database will override their vertebrate rows (except for any extra information they have to offer).

As for "hand-entry," I do almost all my work in spreadsheets. I spent a day or two copying scientific names from the Catalogue of Life into my spreadsheet. Common names and slugs (common names in a URL format) is a project that will probably take years. I might type a scientific name or common name into Google and see where it leads me. If a certain scientific name is associated with the common name "yellow birch," then its slug becomes yellow-birch. If two or more species are called yellow birch, then I enter yellow-birch in a different table ("Floaters"), which leads to a disambiguation page.

For organisms with two or more popular common names - well, I haven't really figured that out yet. I'll probably have to make an extra table for additional names. Catalogue of Life has common names in its database, but they all have upper case first letters - like American Beaver. That works fine for a page title but in regular text I need to make beaver lowercase without changing American. So I'm just starting from square one and recreating all the common names from scratch.

Multiple names can be handled in at least two ways.  A child table of species which has species id and alternate name per record - then you can get all other-names back by species id.  Of course going from altername-name back to species may get you multiple species.  Or, welcome to postgres' arrays-as-column: you can have one column, maybe called aliases which is an array of string.

It gets still more complicated when you get into "specialist names." ;) But the system I've set up so far seems to be working pretty nicely.

On Mon, Oct 26, 2015 at 1:41 PM, Rob Sargent <robjsargent@gmail.com> wrote:
On 10/26/2015 02:29 PM, David Blomstrom wrote:
Sorry for the late response. I don't have Internet access at home, so I only post from the library or a WiFi cafe.

Anyway, where do I begin?

Regarding my "usage patterns," I use spreadsheets (Apple's Numbers program) to organize data. I then save it as a CSV file and import it into a database table. It would be very hard to break with that tradition, because I don't know of any other way to organize my data.

On the other hand, I have a column (Rank) that identifies different taxonomic levels (kingdom, class, etc.). So I can easily sort a table into specific taxonomic levels and save one level at a time for a database table.

There is one problem, though. I can easily put all the vertebrate orders and even families into a table. But genera might be harder, and species probably won't work; there are simply too many. My spreadsheet program is almost overwhelmed by fish species alone. The only solution would be if I could import Mammals.csv, then import Birds.csv, Reptiles.csv, etc. But that might be kind of tedious, especially if I have to make multiple updates.

Yes I suspect you spreadsheet will be limited in rows, but of course you can send all the spreadsheets to a single table in the database. If that's what you want.  You don't have to, but you see mention of tables millions of records routinely.  On the other hand, if performance becomes an issue with the single table approach you might want to look at "partitioning".  But I would be surprised if you had to go there.

What is your data source?  How much hand-entry are you doing? There are tools which (seriously) upgrade the basic 'COPY into <table>' command.


As for "attributes," I'll post my table's schema, with a description, next.





--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
David Blomstrom
Date:
Incidentally, this is the script I'm using to display pages. It queries each table twice (field Taxon for scientific names, Slug for common names). It then picks up three auxiliary tables listing "parataxa," floaters and groups.

$sql = "SELECT SUM(num) as num FROM (
  SELECT COUNT(Taxon) AS num FROM gz_life_kingclass WHERE Taxon = :MyURL
  UNION ALL
  SELECT COUNT(Slug) AS num FROM gz_life_kingclass WHERE Slug = :MyURL
  UNION ALL
  SELECT COUNT(Taxon) AS num FROM gz_life_mammals WHERE Taxon = :MyURL
  UNION ALL
  SELECT COUNT(Slug) AS num FROM gz_life_mammals WHERE Slug = :MyURL
  UNION ALL
  SELECT COUNT(Taxon) AS num FROM gz_life_birds WHERE Taxon = :MyURL
  UNION ALL
  SELECT COUNT(Slug) AS num FROM gz_life_birds WHERE Slug = :MyURL
  UNION ALL
  SELECT COUNT(Taxon) AS num FROM gz_life_reptiles WHERE Taxon = :MyURL
  UNION ALL
  SELECT COUNT(Slug) AS num FROM gz_life_reptiles WHERE Slug = :MyURL
  UNION ALL
  SELECT COUNT(Taxon) AS num FROM gz_life_amphibians WHERE Taxon = :MyURL
  UNION ALL
  SELECT COUNT(Slug) AS num FROM gz_life_amphibians WHERE Slug = :MyURL
  UNION ALL
  SELECT COUNT(Taxon) AS num FROM gz_life_fish WHERE Taxon = :MyURL
  UNION ALL
  SELECT COUNT(Slug) AS num FROM gz_life_fish WHERE Slug = :MyURL
  UNION ALL
  SELECT COUNT(Taxon) AS num FROM gz_life_fish2 WHERE Taxon = :MyURL
  UNION ALL
  SELECT COUNT(Slug) AS num FROM gz_life_fish2 WHERE Slug = :MyURL
  UNION ALL
  SELECT COUNT(Taxon) AS num FROM gz_life_inverts WHERE Taxon = :MyURL
  UNION ALL
  SELECT COUNT(Slug) AS num FROM gz_life_inverts WHERE Slug = :MyURL
  UNION ALL
  SELECT COUNT(Taxon) AS num FROM gz_life_plants WHERE Taxon = :MyURL
  UNION ALL
  SELECT COUNT(Slug) AS num FROM gz_life_plants WHERE Slug = :MyURL
  ) AS X";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':MyURL',$MyURL,PDO::PARAM_STR);
$stmt->execute();
$Total = $stmt->fetch();

switch($Total['num'])
{
 case 1:
 require_once($BaseINC."/2b/inc/C/C_Child.php");
 break;

 case 0:
$sql = "SELECT SUM(num) as num FROM (
  SELECT COUNT(Taxon) AS num FROM gz_life_parataxa WHERE Taxon = :MyURL
  UNION ALL
  SELECT COUNT(URL) AS num FROM gz_life_floaters WHERE URL = :MyURL
  UNION ALL
  SELECT COUNT(URL) AS num FROM gz_life_groups WHERE URL = :MyURL
  ) AS X";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':MyURL',$MyURL,PDO::PARAM_STR);
$stmt->execute();
$Total = $stmt->fetch();


On Mon, Oct 26, 2015 at 1:51 PM, David Blomstrom <david.blomstrom@gmail.com> wrote:
I'm focusing primarily on vertebrates at the moment, which have a total of (I think) about 60,000-70,000 rows for all taxons (species, families, etc.). My goal is to create a customized database that does a really good job of handling vertebrates first, manually adding a few key invertebrates and plants as needed.

I couldn't possibly repeat the process with invertebrates or plants, which are simply overwhelming. So, if I ever figure out the Catalogue of Life's database, then I'm simply going to modify its tables so they work with my system. My vertebrates database will override their vertebrate rows (except for any extra information they have to offer).

As for "hand-entry," I do almost all my work in spreadsheets. I spent a day or two copying scientific names from the Catalogue of Life into my spreadsheet. Common names and slugs (common names in a URL format) is a project that will probably take years. I might type a scientific name or common name into Google and see where it leads me. If a certain scientific name is associated with the common name "yellow birch," then its slug becomes yellow-birch. If two or more species are called yellow birch, then I enter yellow-birch in a different table ("Floaters"), which leads to a disambiguation page.

For organisms with two or more popular common names - well, I haven't really figured that out yet. I'll probably have to make an extra table for additional names. Catalogue of Life has common names in its database, but they all have upper case first letters - like American Beaver. That works fine for a page title but in regular text I need to make beaver lowercase without changing American. So I'm just starting from square one and recreating all the common names from scratch.

It gets still more complicated when you get into "specialist names." ;) But the system I've set up so far seems to be working pretty nicely.

On Mon, Oct 26, 2015 at 1:41 PM, Rob Sargent <robjsargent@gmail.com> wrote:
On 10/26/2015 02:29 PM, David Blomstrom wrote:
Sorry for the late response. I don't have Internet access at home, so I only post from the library or a WiFi cafe.

Anyway, where do I begin?

Regarding my "usage patterns," I use spreadsheets (Apple's Numbers program) to organize data. I then save it as a CSV file and import it into a database table. It would be very hard to break with that tradition, because I don't know of any other way to organize my data.

On the other hand, I have a column (Rank) that identifies different taxonomic levels (kingdom, class, etc.). So I can easily sort a table into specific taxonomic levels and save one level at a time for a database table.

There is one problem, though. I can easily put all the vertebrate orders and even families into a table. But genera might be harder, and species probably won't work; there are simply too many. My spreadsheet program is almost overwhelmed by fish species alone. The only solution would be if I could import Mammals.csv, then import Birds.csv, Reptiles.csv, etc. But that might be kind of tedious, especially if I have to make multiple updates.

Yes I suspect you spreadsheet will be limited in rows, but of course you can send all the spreadsheets to a single table in the database. If that's what you want.  You don't have to, but you see mention of tables millions of records routinely.  On the other hand, if performance becomes an issue with the single table approach you might want to look at "partitioning".  But I would be surprised if you had to go there.

What is your data source?  How much hand-entry are you doing? There are tools which (seriously) upgrade the basic 'COPY into <table>' command.


As for "attributes," I'll post my table's schema, with a description, next.





--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
Gavin Flower
Date:
Hi David,

Please don't top post!

On 27/10/15 09:42, David Blomstrom wrote:
> I've created my first table in postgreSQL. I'd like to ask 1) if you
> see any errors, 2) do you have any suggestions for improving it, and
> 3) can you give me the code I need to paste into the shell (or
> whatever you call the command-line tool) to recreate it?
>
> This is what the table's schema looks like in MySQL...
>
> N - int(6) [Primary Key]
> Taxon - varchar(50) [Unique Key]
> Parent - varchar(50) [Index Key]
> ParentID - tinyint(1) [Index Key]
> Slug - varchar(50) [Index Key]
> NameCommon - varchar(50)
> Plural - varchar(50)
> Extinct - tinyint(1)
> Rank - tinyint(2)
> Key - tinyint(1)
>
> The table type is MyIsam, collation is latin1_general_ci
>
> Slug, NameCommon and Plural are NULL.
>
> All of my tables have a default first column named N or ID, which is
> simply a numerical key that begins with 1. It's always designated the
> primary key.
>
> All the other columns in this table can be divided into two
> categories, text (varchar) and numerical (tinyint).
>
> The values in the columns Taxon and Slug serve as URL's, so they can
> have no spaces, apostrophes, accents, etc. (Taxon handles scientific
> names, Slug common names, if any.) So a row focusing on the Steller's
> jay would have values like these:
>
> NameCommmon - Steller’s jay
> Plural - Steller’s jays
> Taxon - Cyanocitta-stelleri
> Slug - stellers-jay
> Parent - Cyanocitta
>
> The column ParentID - which I want to use for hierarchical
> relationships - has values ranging from 1 for Mammalia (the first row)
> to 5 for the species level. The column Extinct has the value 1 (not
> extinct) or 2, 3 or 4 for various categories of extinct taxons.
>
> The column Rank has the value 25 for the first row (class Mammalia),
> 35 for each order (e.g. Carnivora), 45 for each family, 55 for each
> genus and 65 for each species. The value for Key is 1 (for every row),
> designating it a tetrapod. The bird, reptile and amphibian tables have
> the same key value, while fish, invertebrates and plants have their
> own unique keys.
>
> I have Unique keys on N and Taxon, Index keys (not unique) on Parent,
> ParentID and Slug.
>
> My PostgreSQL table is in a database named GeoZoo. When I go into
> pgAdmin3 > SQLPane, it looks like this:
>
> CREATE TABLE public.gz_life_mammals
> (
>   "N" integer NOT NULL,
>   "Taxon" character varying(50) NOT NULL,
>   "Parent" character varying(50) NOT NULL,
>   "ParentID" smallint NOT NULL,
>   "Slug" character varying(50),
>   "NameCommon" character varying(50),
>   "Plural" character varying(50),
>   "Extinct" smallint NOT NULL,
>   "Rank" smallint NOT NULL,
>   "Key" smallint NOT NULL,
>   CONSTRAINT "Primary Key" PRIMARY KEY ("N"),
>   CONSTRAINT "Unique Key" UNIQUE ("Taxon")
> [I haven't added any non-unique keys yet.]
> )
> WITH (
>   OIDS=FALSE
> );
> ALTER TABLE public.gz_life_mammals
>   OWNER TO postgres;
>
> I should also mention that Taxon is the column I use to UNION or JOIN
> this table with other tables.
>
> P.S. If I decide to organize things by taxonomic levels (e.g. kingdom,
> class, etc.), then it should be easy to rename the table, delete a few
> columns, and refill it with data associated with a particular class.
[...]

Would suggest using lower case column names without embedded spaces, if
possible!

NEVER assign tables to the postgres user, application tables should be
owned by a user!

Note that PRIMARY KEY gives you both NON NULL & uniqueness.  So you
don't need a separate PRIMARY KEY constraint!

'id' would be better than 'N' for the primary key name. ==> 'id int
PRIMARY KEY'

Using 'text' rather than 'character varying(50)' would probably be better.

Since you are making a single column unique, suggest 'taxon   text
UNIQUE NOT NULL'

You don't need to specify 'OIDS=FALSE', as that is now the defualt.



Cheers,
Gavin




Re: Recursive Arrays 101

From
David Blomstrom
Date:
What does "top post" mean? And what do you mean by "embedded spaces"? Are you referring to the underscores in the TABLE name?

On Mon, Oct 26, 2015 at 2:12 PM, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:
Hi David,

Please don't top post!


On 27/10/15 09:42, David Blomstrom wrote:
I've created my first table in postgreSQL. I'd like to ask 1) if you see any errors, 2) do you have any suggestions for improving it, and 3) can you give me the code I need to paste into the shell (or whatever you call the command-line tool) to recreate it?

This is what the table's schema looks like in MySQL...

N - int(6) [Primary Key]
Taxon - varchar(50) [Unique Key]
Parent - varchar(50) [Index Key]
ParentID - tinyint(1) [Index Key]
Slug - varchar(50) [Index Key]
NameCommon - varchar(50)
Plural - varchar(50)
Extinct - tinyint(1)
Rank - tinyint(2)
Key - tinyint(1)

The table type is MyIsam, collation is latin1_general_ci

Slug, NameCommon and Plural are NULL.

All of my tables have a default first column named N or ID, which is simply a numerical key that begins with 1. It's always designated the primary key.

All the other columns in this table can be divided into two categories, text (varchar) and numerical (tinyint).

The values in the columns Taxon and Slug serve as URL's, so they can have no spaces, apostrophes, accents, etc. (Taxon handles scientific names, Slug common names, if any.) So a row focusing on the Steller's jay would have values like these:

NameCommmon - Steller&#8217;s jay
Plural - Steller&#8217;s jays
Taxon - Cyanocitta-stelleri
Slug - stellers-jay
Parent - Cyanocitta

The column ParentID - which I want to use for hierarchical relationships - has values ranging from 1 for Mammalia (the first row) to 5 for the species level. The column Extinct has the value 1 (not extinct) or 2, 3 or 4 for various categories of extinct taxons.

The column Rank has the value 25 for the first row (class Mammalia), 35 for each order (e.g. Carnivora), 45 for each family, 55 for each genus and 65 for each species. The value for Key is 1 (for every row), designating it a tetrapod. The bird, reptile and amphibian tables have the same key value, while fish, invertebrates and plants have their own unique keys.

I have Unique keys on N and Taxon, Index keys (not unique) on Parent, ParentID and Slug.

My PostgreSQL table is in a database named GeoZoo. When I go into pgAdmin3 > SQLPane, it looks like this:

CREATE TABLE public.gz_life_mammals
(
  "N" integer NOT NULL,
  "Taxon" character varying(50) NOT NULL,
  "Parent" character varying(50) NOT NULL,
  "ParentID" smallint NOT NULL,
  "Slug" character varying(50),
  "NameCommon" character varying(50),
  "Plural" character varying(50),
  "Extinct" smallint NOT NULL,
  "Rank" smallint NOT NULL,
  "Key" smallint NOT NULL,
  CONSTRAINT "Primary Key" PRIMARY KEY ("N"),
  CONSTRAINT "Unique Key" UNIQUE ("Taxon")
[I haven't added any non-unique keys yet.]
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.gz_life_mammals
  OWNER TO postgres;

I should also mention that Taxon is the column I use to UNION or JOIN this table with other tables.

P.S. If I decide to organize things by taxonomic levels (e.g. kingdom, class, etc.), then it should be easy to rename the table, delete a few columns, and refill it with data associated with a particular class.
[...]

Would suggest using lower case column names without embedded spaces, if possible!

NEVER assign tables to the postgres user, application tables should be owned by a user!

Note that PRIMARY KEY gives you both NON NULL & uniqueness.  So you don't need a separate PRIMARY KEY constraint!

'id' would be better than 'N' for the primary key name. ==> 'id int  PRIMARY KEY'

Using 'text' rather than 'character varying(50)' would probably be better.

Since you are making a single column unique, suggest 'taxon   text UNIQUE NOT NULL'

You don't need to specify 'OIDS=FALSE', as that is now the defualt.



Cheers,
Gavin





--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
Gavin Flower
Date:
On 27/10/15 10:17, David Blomstrom wrote:
> What does "top post" mean? And what do you mean by "embedded spaces"?
> Are you referring to the underscores in the TABLE name?
>
> On Mon, Oct 26, 2015 at 2:12 PM, Gavin Flower
> <GavinFlower@archidevsys.co.nz <mailto:GavinFlower@archidevsys.co.nz>>
> wrote:
>
>     Hi David,
>
>     Please don't top post!
>
[...]

Top posting is when you put your reply at the top of the post, as you
did just now in response to my post.

Here I am bottom posting, which is the norm for postgres mailing lists.

Ignore my comment about embedded spaces, I misread what you had written,
underlines are fine.


Re: Recursive Arrays 101

From
Rob Sargent
Date:
On 10/26/2015 03:21 PM, Gavin Flower wrote:
On 27/10/15 10:17, David Blomstrom wrote:
What does "top post" mean? And what do you mean by "embedded spaces"? Are you referring to the underscores in the TABLE name?

On Mon, Oct 26, 2015 at 2:12 PM, Gavin Flower <GavinFlower@archidevsys.co.nz <mailto:GavinFlower@archidevsys.co.nz>> wrote:

    Hi David,

    Please don't top post!

[...]

Top posting is when you put your reply at the top of the post, as you did just now in response to my post.

Here I am bottom posting, which is the norm for postgres mailing lists.

Ignore my comment about embedded spaces, I misread what you had written, underlines are fine.
But _really_ recommend lowercase column names and table names.  You'll have to quote them every time you want to use them in manual sql or scripts.

Re: Recursive Arrays 101

From
David Blomstrom
Date:
Here's what it looks like now:

CREATE TABLE public.gz_life_mammals
(
  id integer NOT NULL,
  taxon text NOT NULL,
  parent text NOT NULL,
  slug text,
  namecommon text,
  plural text,
  extinct smallint NOT NULL,
  rank smallint NOT NULL,
  key smallint NOT NULL,
  CONSTRAINT "Primary Key" PRIMARY KEY (id),
  CONSTRAINT "Unique Key" UNIQUE (taxon)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.gz_life_mammals
  OWNER TO postgres;

* * * * *

I don't even have a clue what OIDS=FALSE means; I haven't read up on it yet. It's just there by default. I haven't figured out how to change the NULL value for any columns, other than toggle back and forth between NULL and NOT NULL.

To assign a user, would I just ask it to associate a table with my username? Can I do that with pgAdmin3?

Thanks.

Re: Recursive Arrays 101

From
Adrian Klaver
Date:
On 10/26/2015 02:26 PM, David Blomstrom wrote:
> Here's what it looks like now:
>
> CREATE TABLE public.gz_life_mammals
> (
>    id integer NOT NULL,
>    taxon text NOT NULL,
>    parent text NOT NULL,
>    slug text,
>    namecommon text,
>    plural text,
>    extinct smallint NOT NULL,
>    rank smallint NOT NULL,
>    key smallint NOT NULL,
>    CONSTRAINT "Primary Key" PRIMARY KEY (id),
>    CONSTRAINT "Unique Key" UNIQUE (taxon)
> )
> WITH (
>    OIDS=FALSE
> );
> ALTER TABLE public.gz_life_mammals
>    OWNER TO postgres;
>
> * * * * *
>
> I don't even have a clue what OIDS=FALSE means;

That is not necessary. OIDs on user tables are no longer automatically
included, so that is FALSE by default. OID is object id. In the old days
it used to be a default hidden column on all tables. That turned out not
to be a good idea, so they are no longer there for user tables. You will
see then on system tables if you specifically do select oid, * from
some_system_table.

I haven't read up on it
> yet. It's just there by default. I haven't figured out how to change the
> NULL value for any columns, other than toggle back and forth between
> NULL and NOT NULL.

Is this what you are talking about?:

ALTER TABLE some_table ALTER COLUMN SET NOT NULL

or

ALTER TABLE some_table ALTER COLUMN DROP NOT NULL

http://www.postgresql.org/docs/9.4/interactive/sql-altertable.html

>
> To assign a user, would I just ask it to associate a table with my
> username? Can I do that with pgAdmin3?
>
> Thanks.


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Recursive Arrays 101

From
Gavin Flower
Date:
On 27/10/15 10:26, David Blomstrom wrote:
> Here's what it looks like now:
>
> CREATE TABLE public.gz_life_mammals
> (
>   id integer NOT NULL,
>   taxon text NOT NULL,
>   parent text NOT NULL,
>   slug text,
>   namecommon text,
>   plural text,
>   extinct smallint NOT NULL,
>   rank smallint NOT NULL,
>   key smallint NOT NULL,
>   CONSTRAINT "Primary Key" PRIMARY KEY (id),
>   CONSTRAINT "Unique Key" UNIQUE (taxon)
> )
> WITH (
>   OIDS=FALSE
> );
> ALTER TABLE public.gz_life_mammals
>   OWNER TO postgres;
>
> * * * * *
>
> I don't even have a clue what OIDS=FALSE means; I haven't read up on
> it yet. It's just there by default. I haven't figured out how to
> change the NULL value for any columns, other than toggle back and
> forth between NULL and NOT NULL.
>
> To assign a user, would I just ask it to associate a table with my
> username? Can I do that with pgAdmin3?
>
> Thanks.
Hi David,

Constructing SQL in an editor and executing the SQL script using psql is
often a lot easier than using pgadmin3, and gives you far more control!
I use both, but more often use psql.

 From the postgres user and using psql, you can create a user & database
like:

CREATE ROLE gavin
LOGIN
CREATEDB;

CREATE DATABASE gavin
OWNER gavin;


Obviously, you can create a database with a different name for the same
user.  Just that the above means that if you call up psql from a
terminal of that user, you don't need to explicitly tell it what
database to use.

I created an SQL script create_table.sql (usually better to have a more
descriptive name!) in an editor:

CREATE TABLE public.gz_life_mammals
(
   id            int PRIMARY KEY,
   taxon         text UNIQUE NOT NULL,
   parent        text NOT NULL,
   slug          text,
   name_common   text,
   plural        text,
   extinct       smallint NOT NULL,
   rank          smallint NOT NULL,
   key           smallint NOT NULL
);

Here is a session where I create the table (I created the terminal in
the same directory as the SQL script, you can also simply cd to the
relevant directory before executing psql):
$ psql
psql (9.4.4)
Type "help" for help.

gavin=> \i create_table.sql
CREATE TABLE
gavin=> \q
$

You might be able to do all the above using pgadmin3...


Cheers,
Gavin



Re: Recursive Arrays 101

From
Adrian Klaver
Date:
On 10/26/2015 01:51 PM, David Blomstrom wrote:
> I'm focusing primarily on vertebrates at the moment, which have a total
> of (I think) about 60,000-70,000 rows for all taxons (species, families,
> etc.). My goal is to create a customized database that does a really
> good job of handling vertebrates first, manually adding a few key
> invertebrates and plants as needed.
>
> I couldn't possibly repeat the process with invertebrates or plants,
> which are simply overwhelming. So, if I ever figure out the Catalogue of
> Life's database, then I'm simply going to modify its tables so they work
> with my system. My vertebrates database will override their vertebrate
> rows (except for any extra information they have to offer).
>
> As for "hand-entry," I do almost all my work in spreadsheets. I spent a
> day or two copying scientific names from the Catalogue of Life into my
> spreadsheet. Common names and slugs (common names in a URL format) is a
> project that will probably take years. I might type a scientific name or
> common name into Google and see where it leads me. If a certain
> scientific name is associated with the common name "yellow birch," then
> its slug becomes yellow-birch. If two or more species are called yellow
> birch, then I enter yellow-birch in a different table ("Floaters"),
> which leads to a disambiguation page.
>
> For organisms with two or more popular common names - well, I haven't
> really figured that out yet. I'll probably have to make an extra table
> for additional names. Catalogue of Life has common names in its
> database, but they all have upper case first letters - like American
> Beaver. That works fine for a page title but in regular text I need to
> make beaver lowercase without changing American. So I'm just starting
> from square one and recreating all the common names from scratch.

I think there has to be a better way as this is just a formatting issue
Can't remember what programming language you are working in, but in Python:

In [13]: s = 'American Beaver'

In [14]: s.capitalize()
Out[14]: 'American beaver'

In [15]: s.lower()
Out[15]: 'american beaver'

>
> It gets still more complicated when you get into "specialist names." ;)
> But the system I've set up so far seems to be working pretty nicely.
>
> On Mon, Oct 26, 2015 at 1:41 PM, Rob Sargent <robjsargent@gmail.com
> <mailto:robjsargent@gmail.com>> wrote:
>
>     On 10/26/2015 02:29 PM, David Blomstrom wrote:
>
>         Sorry for the late response. I don't have Internet access at
>         home, so I only post from the library or a WiFi cafe.
>
>         Anyway, where do I begin?
>
>         Regarding my "usage patterns," I use spreadsheets (Apple's
>         Numbers program) to organize data. I then save it as a CSV file
>         and import it into a database table. It would be very hard to
>         break with that tradition, because I don't know of any other way
>         to organize my data.
>
>         On the other hand, I have a column (Rank) that identifies
>         different taxonomic levels (kingdom, class, etc.). So I can
>         easily sort a table into specific taxonomic levels and save one
>         level at a time for a database table.
>
>         There is one problem, though. I can easily put all the
>         vertebrate orders and even families into a table. But genera
>         might be harder, and species probably won't work; there are
>         simply too many. My spreadsheet program is almost overwhelmed by
>         fish species alone. The only solution would be if I could import
>         Mammals.csv, then import Birds.csv, Reptiles.csv, etc. But that
>         might be kind of tedious, especially if I have to make multiple
>         updates.
>
>     Yes I suspect you spreadsheet will be limited in rows, but of course
>     you can send all the spreadsheets to a single table in the database.
>     If that's what you want.  You don't have to, but you see mention of
>     tables millions of records routinely.  On the other hand, if
>     performance becomes an issue with the single table approach you
>     might want to look at "partitioning".  But I would be surprised if
>     you had to go there.
>
>     What is your data source?  How much hand-entry are you doing? There
>     are tools which (seriously) upgrade the basic 'COPY into <table>'
>     command.
>
>
>         As for "attributes," I'll post my table's schema, with a
>         description, next.
>
>
>
>
>
> --
> David Blomstrom
> Writer & Web Designer (Mac, M$ & Linux)
> www.geobop.org <http://www.geobop.org>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Recursive Arrays 101

From
David Blomstrom
Date:
OK, I figured out how to drop the primary key and change the NULLS. So it looks like this now:

CREATE TABLE public.gz_life_mammals
(
  id integer NOT NULL,
  taxon text NOT NULL,
  parent text NOT NULL,
  slug text,
  namecommon text,
  plural text,
  extinct smallint NOT NULL,
  rank smallint NOT NULL,
  key smallint NOT NULL,
  CONSTRAINT "Unique Key" UNIQUE (taxon)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.gz_life_mammals
  OWNER TO postgres;

Re: Recursive Arrays 101

From
David Blomstrom
Date:
I pasted this into the shell...

CREATE ROLE david
LOGIN
CREATEDB;

CREATE DATABASE GeoZoo2
OWNER david;

and I did it again, replacing LOGIN with my password, but when I refresh pgAdmin III, there are no new databases.

And when I paste this in, it doesn't create a table...

CREATE TABLE public.gz_life_mammals2
(
  id            int PRIMARY KEY,
  taxon         text UNIQUE NOT NULL,
  parent        text NOT NULL,
  slug          text,
  name_common   text,
  plural        text,
  extinct       smallint NOT NULL,
  rank          smallint NOT NULL,
  key           smallint NOT NULL
);

Re: Recursive Arrays 101

From
Adrian Klaver
Date:
On 10/26/2015 03:18 PM, David Blomstrom wrote:
> I pasted this into the shell...

What shell, psql?

>
> CREATE ROLE david
> LOGIN
> CREATEDB;
>
> CREATE DATABASE GeoZoo2
> OWNER david;

Did you see any errors?

>
> and I did it again, replacing LOGIN with my password, but when I refresh
> pgAdmin III, there are no new databases.

No LOGIN is an attribute of ROLE it is not the password
>
> And when I paste this in, it doesn't create a table...
>
> CREATE TABLE public.gz_life_mammals2
> (
>    id            int PRIMARY KEY,
>    taxon         text UNIQUE NOT NULL,
>    parent        text NOT NULL,
>    slug          text,
>    name_common   text,
>    plural        text,
>    extinct       smallint NOT NULL,
>    rank          smallint NOT NULL,
>    key           smallint NOT NULL
> );


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Recursive Arrays 101

From
Gavin Flower
Date:
On 27/10/15 09:29, David Blomstrom wrote:
[...]
> Regarding my "usage patterns," I use spreadsheets (Apple's Numbers
> program) to organize data. I then save it as a CSV file and import it
> into a database table. It would be very hard to break with that
> tradition, because I don't know of any other way to organize my data.
>
> On the other hand, I have a column (Rank) that identifies different
> taxonomic levels (kingdom, class, etc.). So I can easily sort a table
> into specific taxonomic levels and save one level at a time for a
> database table.
>
> There is one problem, though. I can easily put all the vertebrate
> orders and even families into a table. But genera might be harder, and
> species probably won't work; there are simply too many. My spreadsheet
> program is almost overwhelmed by fish species alone.
[...]

The maximum number of rows in the LibreOffice spreadsheet (Calc) is
1,073,741,824

LibreOffice is free and available for Apple Macs (as well as for Linux &
Microsoft O/S's), see
     http://www.libreoffice.org/download
I suggest you download the 5.0 version


Cheers,
Gavin



Re: Recursive Arrays 101

From
Gavin Flower
Date:
On 27/10/15 11:18, David Blomstrom wrote:
> I pasted this into the shell...
>
> CREATE ROLE david
> LOGIN
> CREATEDB;
>
> CREATE DATABASE GeoZoo2
> OWNER david;
>
> and I did it again, replacing LOGIN with my password, but when I
> refresh pgAdmin III, there are no new databases.
>
> And when I paste this in, it doesn't create a table...
>
> CREATE TABLE public.gz_life_mammals2
> (
>   id            int PRIMARY KEY,
>   taxon         text UNIQUE NOT NULL,
>   parent        text NOT NULL,
>   slug          text,
>   name_common   text,
>   plural        text,
>   extinct       smallint NOT NULL,
>   rank          smallint NOT NULL,
>   key           smallint NOT NULL
> );
You can list all the databases with '\l' and the tables with '\dt' in
psql, for example:

$ psql
psql (9.4.4)
Type "help" for help.

gavin=> \l
                                        List of databases
         Name        |   Owner    | Encoding |   Collate |    Ctype
|   Access privileges
--------------------+------------+----------+-------------+-------------+-----------------------
  gavin              | gavin      | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
  greeter_quickstart | jboss_dev  | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
  jboss_dev_db       | jboss_dev  | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
  jboss_sys_db       | jboss_sys  | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
  jboss_test_db      | jboss_test | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
  mydb               | mydb_admin | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
  pgsp2ed            | gavin      | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
  postgres           | postgres   | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
  postgresql_book    | gavin      | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
  template0          | postgres   | UTF8     | en_GB.UTF-8 | en_GB.UTF-8
| =c/postgres          +
                     |            |          | |             |
postgres=CTc/postgres
  template1          | postgres   | UTF8     | en_GB.UTF-8 | en_GB.UTF-8
| =c/postgres          +
                     |            |          | |             |
postgres=CTc/postgres
(11 rows)

gavin=> \c pgsp2ed
You are now connected to database "pgsp2ed" as user "gavin".
pgsp2ed=> \dt
                   List of relations
  Schema |           Name           | Type  |  Owner
--------+--------------------------+-------+----------
  public | accounts                 | table | gavin
  public | application_settings_new | table | gavin
  public | application_settings_old | table | gavin
  public | fiverow                  | table | gavin
  public | fiverows                 | table | gavin
  public | fruit                    | table | gavin
  public | fruit_in_stock           | table | gavin
  public | fruit_offer              | table | gavin
  public | modified_table           | table | gavin
  public | modify_test              | table | gavin
  public | my_data                  | table | gavin
  public | name                     | table | gavin
  public | names                    | table | gavin
  public | namex                    | table | gavin
  public | notify_test              | table | gavin
  public | original_database        | table | gavin
  public | original_user            | table | gavin
  public | salary                   | table | fred
  public | salary_change_log        | table | fred
  public | test                     | table | postgres
  public | test1                    | table | gavin
  public | test2                    | table | gavin
  public | test3                    | table | gavin
  public | tmp_setting              | table | gavin
  public | word                     | table | gavin
(25 rows)

pgsp2ed=> \q
$



Cheers,
Gavin


Re: Recursive Arrays 101

From
Gavin Flower
Date:
On 27/10/15 11:18, David Blomstrom wrote:
[...]
> CREATE DATABASE GeoZoo2
[...]

Would strongly advise NOT using capital letters in names of databases in pg!



Re: Recursive Arrays 101

From
David Blomstrom
Date:
When I type in /l, it just says "database Postgres," even though I can see TWO databases in pgAdmin III. When I type in /dt, it says Username [postgres].

On Mon, Oct 26, 2015 at 3:40 PM, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:
On 27/10/15 11:18, David Blomstrom wrote:
[...]
CREATE DATABASE GeoZoo2
[...]

Would strongly advise NOT using capital letters in names of databases in pg!




--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
Gavin Flower
Date:
On 27/10/15 11:57, David Blomstrom wrote:
> When I type in /l, it just says "database Postgres," even though I can
> see TWO databases in pgAdmin III. When I type in /dt, it says Username
> [postgres].
>
> On Mon, Oct 26, 2015 at 3:40 PM, Gavin Flower
> <GavinFlower@archidevsys.co.nz <mailto:GavinFlower@archidevsys.co.nz>>
> wrote:
>
>     On 27/10/15 11:18, David Blomstrom wrote:
>     [...]
>
>         CREATE DATABASE GeoZoo2
>
>     [...]
>
>     Would strongly advise NOT using capital letters in names of
>     databases in pg!
>
>
>
>
> --
> David Blomstrom
> Writer & Web Designer (Mac, M$ & Linux)
> www.geobop.org <http://www.geobop.org>

What does '\l' and '\du' actually give you (gives us the actual output)?


Re: Recursive Arrays 101

From
Adrian Klaver
Date:
On 10/26/2015 03:57 PM, David Blomstrom wrote:
> When I type in /l, it just says "database Postgres," even though I can
> see TWO databases in pgAdmin III. When I type in /dt, it says Username
> [postgres].

Huh. It should be \l and \dt
Step us through what you are doing, using cut and paste directly from
the terminal.

So something like:

aklaver@panda:~> psql -d test -U aklaver -h localhost

psql (9.4.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)
Type "help" for help.

test=>

test=> \l
                                       List of databases
       Name       |   Owner    | Encoding |   Collate   |    Ctype    |
   Access privileges
-----------------+------------+----------+-------------+-------------+-----------------------
  business_app    | app_admin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
  hplc            | hplc_admin | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
  postgres        | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
  production      | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
  template0       | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres          +
                  |            |          |             |             |
postgres=CTc/postgres
  template1       | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres          +
                  |            |          |             |             |
postgres=CTc/postgres
  test            | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
  test_hplc       | hplc_admin | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
  test_production | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
  track_stocks    | aklaver    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(10 rows)

test=> \dt
             List of relations
  Schema |     Name     | Type  |  Owner
--------+--------------+-------+----------
  public | app_sessions | table | postgres
  public | app_users    | table | postgres
  public | cp_test      | table | aklaver
  public | data         | table | aklaver
  public | date_test    | table | postgres
  public | excl_test    | table | aklaver
  public | fk_rel       | table | postgres
  public | foo          | table | aklaver
  public | inet_test    | table | postgres
  public | json_test    | table | postgres
  public | lat_long     | table | postgres
  public | orig_test    | table | aklaver
  public | pk_rel       | table | postgres
  public | pr_test      | table | aklaver
  public | rowtype_test | table | aklaver
  public | sub_item     | table | postgres
  public | ts_test      | table | postgres
  public | users        | table | aklaver


>
> On Mon, Oct 26, 2015 at 3:40 PM, Gavin Flower
> <GavinFlower@archidevsys.co.nz <mailto:GavinFlower@archidevsys.co.nz>>
> wrote:
>
>     On 27/10/15 11:18, David Blomstrom wrote:
>     [...]
>
>         CREATE DATABASE GeoZoo2
>
>     [...]
>
>     Would strongly advise NOT using capital letters in names of
>     databases in pg!
>
>
>
>
> --
> David Blomstrom
> Writer & Web Designer (Mac, M$ & Linux)
> www.geobop.org <http://www.geobop.org>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Recursive Arrays 101

From
David Blomstrom
Date:

Server [localhost]: /l

Database [postgres]:

* * * * *

Server [localhost]: /dt

Database [postgres]:

* * * * *

However...I've noticed that when I open up the shell, I get multiple instances - sometimes over half a dozen. If I type the same things into one of the other instances, I get this:

Press <return> to continue.../l

logout

Saving session...

...copying shared history...

...saving history...truncating history files...

...completed.

[Process completed]

* * * * *

Username [postgres]: /dt

psql: invalid port number: "/dt"

Press <return> to continue...


Re: Recursive Arrays 101

From
Gavin Flower
Date:
On 27/10/15 11:18, David Blomstrom wrote:
> I pasted this into the shell...
>
> CREATE ROLE david
> LOGIN
> CREATEDB;
>
> CREATE DATABASE GeoZoo2
> OWNER david;
>
> and I did it again, replacing LOGIN with my password, but when I
> refresh pgAdmin III, there are no new databases.
>
> And when I paste this in, it doesn't create a table...
>
> CREATE TABLE public.gz_life_mammals2
> (
>   id            int PRIMARY KEY,
>   taxon         text UNIQUE NOT NULL,
>   parent        text NOT NULL,
>   slug          text,
>   name_common   text,
>   plural        text,
>   extinct       smallint NOT NULL,
>   rank          smallint NOT NULL,
>   key           smallint NOT NULL
> );
trying typing into psql, assuming this is a postgres user session:
\c geozoo2 david
(format is '\c DATABASE USERID').

If there is an error, please copy & paste the exact error message returned!



Re: Recursive Arrays 101

From
Adrian Klaver
Date:
On 10/26/2015 04:10 PM, David Blomstrom wrote:

The below makes no sense to me.

> Server [localhost]: /l
>
> Database [postgres]:
>
> * * * * *
>
> Server [localhost]: /dt
>
> Database [postgres]:
>
> * * * * *
>
> However...I've noticed that when I open up the shell,

Again, what shell?

>I get multiple
> instances - sometimes over half a dozen. If I type the same things into
> one of the other instances, I get this:
>
> Press <return> to continue.../l
>
> logout
>
> Saving session...
>
> ...copying shared history...
>
> ...saving history...truncating history files...
>
> ...completed.
>
> [Process completed]
>
> * * * * *
>
> Username [postgres]: /dt
>
> psql: invalid port number: "/dt"
>
> Press <return> to continue...
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Recursive Arrays 101

From
David Blomstrom
Date:
Oops, let me try it again...

Shell1

Server [localhost]: \l

Database [postgres]: 

* * * * *

Server [localhost]: \dt

Database [postgres]: 


Shell2

Database [postgres]: \l

Port [5432]: 

* * * * *

Port [5432]: \dt

Username [postgres]: 


Re: Recursive Arrays 101

From
Adrian Klaver
Date:
On 10/26/2015 04:13 PM, David Blomstrom wrote:
> Oops, let me try it again...

Still not making sense.

Show the exact command you are using to get the below and explain where
you are running it.

>
> *Shell1*
>
> Server [localhost]: \l
>
> Database [postgres]:
>
> * * * * *
>
> Server [localhost]: \dt
>
> Database [postgres]:
>
>
> *Shell2*
>
> Database [postgres]: \l
>
> Port [5432]:
>
> * * * * *
>
> Port [5432]: \dt
>
> Username [postgres]:
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Recursive Arrays 101

From
John R Pierce
Date:
On 10/26/2015 4:13 PM, David Blomstrom wrote:
Oops, let me try it again...

Shell1

Server [localhost]: \l

Database [postgres]: 


its asking you for the server host to log onto, and what database on that host.  you can't issue commands, including metacommands like \l, until you've logged on.



-- 
john r pierce, recycling bits in santa cruz

Re: Recursive Arrays 101

From
"David G. Johnston"
Date:
On Mon, Oct 26, 2015 at 7:13 PM, David Blomstrom <david.blomstrom@gmail.com> wrote:
Oops, let me try it again...

Shell1

Server [localhost]: \l

Database [postgres]: 

* * * * *

Server [localhost]: \dt

Database [postgres]: 


Shell2

Database [postgres]: \l

Port [5432]: 

* * * * *

Port [5432]: \dt

Username [postgres]: 


​Please realize that what you show above is non-standard.  What it appears to be doing is walking you through the process of connecting to a database in a step-by-step manner.  What you are showing are prompts asking for a specific piece of data.  The first prompt is asking for the host you wish to connect to with a default value of localhost.  Then port, with a default of 5432.  Database, default postgres.  Username, default postgres.  Assuming all of the defaults are valid what happens if you simply hit enter for each of the prompts until you get to a prompt that is for something other than a setting with a default value?

Typing in \l or \dt at one of the above prompts is non-sensicial.  Whatever you are running is not yet ready to accept generic commands.  Likely even when the prompting is completed what you end up with will not be a generic psql shell where these meta-commands will have meaning.

You should learn what "Shell" (its seems more like an application) you are using and ask whomever wrote it what you should be doing.

David J.

Re: Recursive Arrays 101

From
David Blomstrom
Date:


On Mon, Oct 26, 2015 at 4:19 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/26/2015 04:13 PM, David Blomstrom wrote:
Oops, let me try it again...

Still not making sense.

Show the exact command you are using to get the below and explain where you are running it.


*Shell1*

Server [localhost]: \l

Database [postgres]:

* * * * *

Server [localhost]: \dt

Database [postgres]:


*Shell2*

Database [postgres]: \l

Port [5432]:

* * * * *

Port [5432]: \dt

Username [postgres]:




--
Adrian Klaver
adrian.klaver@aklaver.com


When I click on the SQL Shell (PSQL) icon, it opens two instances. This time, they both gave the same results when I typed in \l and \dt...

Server [localhost]: \l

Database [postgres]: \dt

Port [5432]: 

Re: Recursive Arrays 101

From
Gavin Flower
Date:
On 27/10/15 12:15, David Blomstrom wrote:
>
> Server [localhost]: \c geozoo2 david
>
> Database [postgres]:
>
>
> *Shell2*
>
> Username [postgres]: \c geozoo2 david
>
> psql: warning: extra command-line argument "david" ignored
>
> psql: warning: extra command-line argument "l" ignored
>
> psql: invalid port number: "dt"
>
>
> Press <return> to continue...
>

In my psql seesion I get...

gavin=> \c mydb mydb_admin
You are now connected to database "mydb" as user "mydb_admin".
mydb=>


If you have multiple psql sessions, suggest you delete all except 1 or 2.

By type of shell, is meant are using a bash shell in your terminal, or
csh, or something else? Bash stands for BOurne Again Shell, it process
commands like 'psql' that you type into the shell.

Please copy i the email addresses of the other helping you & the mailing
list!


Re: Recursive Arrays 101

From
John R Pierce
Date:
On 10/26/2015 4:22 PM, Gavin Flower wrote:
>
> By type of shell, is meant are using a bash shell in your terminal, or
> csh, or something else? Bash stands for BOurne Again Shell, it process
> commands like 'psql' that you type into the shell.
>
> Please copy i the email addresses of the other helping you & the
> mailing list!

he said some time ago, he's running MS Windows, with EnterpriseDB's
installation package of PostgreSQL.   'SQL Shell' is a Start Menu
item/shortcut that references a batch script,
D:\PostgreSQL\9.3\scripts\runpsql.bat   which in turn looks like...

@echo off
REM Copyright (c) 2012-2014, EnterpriseDB Corporation.  All rights reserved

REM PostgreSQL server psql runner script for Windows

SET server=localhost
SET /P server="Server [%server%]: "

SET database=postgres
SET /P database="Database [%database%]: "

SET port=5432
SET /P port="Port [%port%]: "

SET username=postgres
SET /P username="Username [%username%]: "

for /f "delims=" %%a in ('chcp ^|find /c "932"') do @ SET
CLIENTENCODING_JP=%%a
if "%CLIENTENCODING_JP%"=="1" SET PGCLIENTENCODING=SJIS
if "%CLIENTENCODING_JP%"=="1" SET /P PGCLIENTENCODING="Client Encoding
[%PGCLIENTENCODING%]: "

REM Run psql
"D:\PostgreSQL\9.3\bin\psql.exe" -h %server% -U %username% -d %database%
-p %port%

pause

--
john r pierce, recycling bits in santa cruz



Re: Recursive Arrays 101

From
David Blomstrom
Date:
LOL - This is precisely why I prefer GUI's. ;)

I have two PostgreSQL icons on my taskbar - a blue elephant (pgAdmin III) and a little monitor (PSQL). When I click on PSQL, it always opens at least two windows or instances. When I type things in and hit enter, it spawns more windows. I've counted at least as many as a dozen.

Sometimes a window will stop working: nothing happens when I type something and hit enter. In that event, I have to force close PSQL and start from scratch.

When I open pgAdmin III, a second icon appears on the taskbar. However, it appears that they're one and the same; just a fluke, I guess.

On Mon, Oct 26, 2015 at 4:22 PM, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:
On 27/10/15 12:15, David Blomstrom wrote:

Server [localhost]: \c geozoo2 david

Database [postgres]:


*Shell2*

Username [postgres]: \c geozoo2 david

psql: warning: extra command-line argument "david" ignored

psql: warning: extra command-line argument "l" ignored

psql: invalid port number: "dt"


Press <return> to continue...


In my psql seesion I get...

gavin=> \c mydb mydb_admin
You are now connected to database "mydb" as user "mydb_admin".
mydb=>


If you have multiple psql sessions, suggest you delete all except 1 or 2.

By type of shell, is meant are using a bash shell in your terminal, or csh, or something else? Bash stands for BOurne Again Shell, it process commands like 'psql' that you type into the shell.

Please copy i the email addresses of the other helping you & the mailing list!



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
David Blomstrom
Date:
No, I'm on a Mac running OS X El Capitan.

Re: Recursive Arrays 101

From
John R Pierce
Date:
On 10/26/2015 4:27 PM, David Blomstrom wrote:
> I have two PostgreSQL icons on my taskbar - a blue elephant (pgAdmin
> III) and a little monitor (PSQL). When I click on PSQL, it always
> opens at least two windows or instances. When I type things in and hit
> enter, it spawns more windows. I've counted at least as many as a dozen.
>
> Sometimes a window will stop working: nothing happens when I type
> something and hit enter. In that event, I have to force close PSQL and
> start from scratch.

thats VERY odd.   I just used SQL Shell (psql) on my Windows system, I
get one window with that batch file prompting for host etc...

--
john r pierce, recycling bits in santa cruz



Re: Recursive Arrays 101

From
Adrian Klaver
Date:
On 10/26/2015 04:26 PM, John R Pierce wrote:
> On 10/26/2015 4:22 PM, Gavin Flower wrote:
>>
>> By type of shell, is meant are using a bash shell in your terminal, or
>> csh, or something else? Bash stands for BOurne Again Shell, it process
>> commands like 'psql' that you type into the shell.
>>
>> Please copy i the email addresses of the other helping you & the
>> mailing list!
>
> he said some time ago, he's running MS Windows, with EnterpriseDB's
> installation package of PostgreSQL.   'SQL Shell' is a Start Menu
> item/shortcut that references a batch script,

Actually OS X :

http://www.postgresql.org/message-id/CAA54Z0hdYkqDDYP=8S577rWvZ4QrN9+-mjKEYRot69um3RasgA@mail.gmail.com


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Recursive Arrays 101

From
David Blomstrom
Date:
I thought PSQL seemed awfully similar to my Apple Terminal. I just noticed that when I click on the PSQL icon, it opens an Apple Terminal icon in the taskbar. I think that's why I'm getting different results - I'm typing into a PostgreSQL terminal and an Apple terminal.

On Mon, Oct 26, 2015 at 4:30 PM, John R Pierce <pierce@hogranch.com> wrote:
On 10/26/2015 4:27 PM, David Blomstrom wrote:
I have two PostgreSQL icons on my taskbar - a blue elephant (pgAdmin III) and a little monitor (PSQL). When I click on PSQL, it always opens at least two windows or instances. When I type things in and hit enter, it spawns more windows. I've counted at least as many as a dozen.

Sometimes a window will stop working: nothing happens when I type something and hit enter. In that event, I have to force close PSQL and start from scratch.

thats VERY odd.   I just used SQL Shell (psql) on my Windows system, I get one window with that batch file prompting for host etc...

--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
Adrian Klaver
Date:
On 10/26/2015 04:21 PM, David Blomstrom wrote:
>
>
> On Mon, Oct 26, 2015 at 4:19 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 10/26/2015 04:13 PM, David Blomstrom wrote:
>
>         Oops, let me try it again...
>
>
>     Still not making sense.
>
>     Show the exact command you are using to get the below and explain
>     where you are running it.
>
>
>         *Shell1*
>
>         Server [localhost]: \l
>
>         Database [postgres]:
>
>         * * * * *
>
>         Server [localhost]: \dt
>
>         Database [postgres]:
>
>
>         *Shell2*
>
>         Database [postgres]: \l
>
>         Port [5432]:
>
>         * * * * *
>
>         Port [5432]: \dt
>
>         Username [postgres]:
>
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
>
> When I click on the SQL Shell (PSQL) icon, it opens two instances. This
> time, they both gave the same results when I typed in \l and \dt...
>
> Server [localhost]: \l
>
> Database [postgres]: \dt
>
> Port [5432]:
>

Alright so it is prompting for the connection information. Just hit
enter to each prompt, it might ask for a password after the above. At
that point you should actually be in psql and then can run the schema
creation commands.

--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Recursive Arrays 101

From
David Blomstrom
Date:
I just deleted the PSQL icon from the dock, so I have to go into Applications > PostgreSQL and open it. When I click on the PSQL icon, it opens my Apple terminal, with the following text:

Last login: Mon Oct 26 16:35:25 on ttys002

/Library/PostgreSQL/9.5/scripts/runpsql.sh; exit

Davids-MacBook-Pro-2:~ davidblomstrom$ /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit

Server [localhost]: 



On Mon, Oct 26, 2015 at 4:33 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/26/2015 04:26 PM, John R Pierce wrote:
On 10/26/2015 4:22 PM, Gavin Flower wrote:

By type of shell, is meant are using a bash shell in your terminal, or
csh, or something else? Bash stands for BOurne Again Shell, it process
commands like 'psql' that you type into the shell.

Please copy i the email addresses of the other helping you & the
mailing list!

he said some time ago, he's running MS Windows, with EnterpriseDB's
installation package of PostgreSQL.   'SQL Shell' is a Start Menu
item/shortcut that references a batch script,

Actually OS X :

http://www.postgresql.org/message-id/CAA54Z0hdYkqDDYP=8S577rWvZ4QrN9+-mjKEYRot69um3RasgA@mail.gmail.com


--
Adrian Klaver
adrian.klaver@aklaver.com



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
Rob Sargent
Date:
On 10/26/2015 05:28 PM, David Blomstrom wrote:
No, I'm on a Mac running OS X El Capitan.


I don't have my mac with me today so this is a little rough.

Cmd-Spacebar
Terminal

This should find the actual normal terminal.

Click on it.

at the prompt therein: psql --username davdi --host localhost

this should connect you to the db, I'm not sure what the prompt looks like

\l
will list the databases

\c <dbname>
\dt will list tables



Re: Recursive Arrays 101

From
Gavin Flower
Date:
On 27/10/15 12:27, David Blomstrom wrote:
> LOL - This is precisely why I prefer GUI's. ;)
>
> I have two PostgreSQL icons on my taskbar - a blue elephant (pgAdmin
> III) and a little monitor (PSQL). When I click on PSQL, it always
> opens at least two windows or instances. When I type things in and hit
> enter, it spawns more windows. I've counted at least as many as a dozen.
>
[...]

Please DO NOT top post!!!

I use both GUI's & terminals.

The 'don't worry your tiny little mind' philosophy of Apple is an
anathema to me - it tries to hide too much of the useful stuff from
users.  I use the 'Mate' Desktop (http://mate-desktop.org) Environment
from Fedora Linux - I have 35 virtual desktops each with 2 highly
configured panels that auto hide, and both my terminals and directory
windows allow multiple tabs (just like web browsers). Apple does not
permit such extensive customisation.

To get a proper terminal in an Apple Mac, have a look at:

https://en.wikipedia.org/wiki/Terminal_%28OS_X%29
http://www.macworld.co.uk/feature/mac-software/get-more-out-of-os-x-terminal-3608274

Note that it uses the bash shell, see:
https://www.gnu.org/software/bash


Re: Recursive Arrays 101

From
David Blomstrom
Date:

Last login: Mon Oct 26 16:35:25 on ttys002

/Library/PostgreSQL/9.5/scripts/runpsql.sh; exit

Davids-MacBook-Pro-2:~ davidblomstrom$ /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit

Server [localhost]: \l

Database [postgres]: \dt

Port [5432]: 

Username [postgres]: Cmd-Spacebar

Terminalpsql: could not translate host name "l" to address: nodename nor servname provided, or not known


Press <return> to continue...

logout

Saving session...

...copying shared history...

...saving history...truncating history files...

...completed.


[Process completed]


* * * * * *


After that it froze; I can't type anything else.


Re: Recursive Arrays 101

From
"David G. Johnston"
Date:
On Mon, Oct 26, 2015 at 7:36 PM, David Blomstrom <david.blomstrom@gmail.com> wrote:
I just deleted the PSQL icon from the dock, so I have to go into Applications > PostgreSQL and open it. When I click on the PSQL icon, it opens my Apple terminal, with the following text:

Last login: Mon Oct 26 16:35:25 on ttys002

/Library/PostgreSQL/9.5/scripts/runpsql.sh; exit

Davids-MacBook-Pro-2:~ davidblomstrom$ /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit

Server [localhost]: 


​So your Mac has this same connection helper script as Windows.  Once you get connected to the actual "psql" prompt you can start entering the commands you were given.  The correct values for the prompts would depend on your setup but it cannot hurt to simply accept all defaults and see what happens.

David J.
 

Re: Recursive Arrays 101

From
Adrian Klaver
Date:
On 10/26/2015 04:42 PM, David Blomstrom wrote:
> Last login: Mon Oct 26 16:35:25 on ttys002
>
> /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit
>
> Davids-MacBook-Pro-2:~ davidblomstrom$
> /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit
>
> Server [localhost]: \l
>
> Database [postgres]: \dt
>
> Port [5432]:
>
> Username [postgres]: Cmd-Spacebar

You are mixing instructions. Do:

Server [localhost]:

Database [postgres]:

Port [5432]:

Username [postgres]:

where you just hit Enter after each prompt above. The [parameter]s are
default values and I would just accept them for now. Once you get
through the prompts it will open a terminal with psql loaded.


>
> Terminalpsql: could not translate host name "l" to address: nodename nor
> servname provided, or not known
>
>
> Press <return> to continue...
>
> logout
>
> Saving session...
>
> ...copying shared history...
>
> ...saving history...truncating history files...
>
> ...completed.
>
>
> [Process completed]
>
>
> * * * * * *
>
>
> After that it froze; I can't type anything else.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Recursive Arrays 101

From
David Blomstrom
Date:


On Mon, Oct 26, 2015 at 5:02 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/26/2015 04:42 PM, David Blomstrom wrote:
Last login: Mon Oct 26 16:35:25 on ttys002

/Library/PostgreSQL/9.5/scripts/runpsql.sh; exit

Davids-MacBook-Pro-2:~ davidblomstrom$
/Library/PostgreSQL/9.5/scripts/runpsql.sh; exit

Server [localhost]: \l

Database [postgres]: \dt

Port [5432]:

Username [postgres]: Cmd-Spacebar

You are mixing instructions. Do:

Server [localhost]:

Database [postgres]:

Port [5432]:

Username [postgres]:

where you just hit Enter after each prompt above. The [parameter]s are default values and I would just accept them for now. Once you get through the prompts it will open a terminal with psql loaded.

 

Last login: Mon Oct 26 16:48:35 on ttys002

/Library/PostgreSQL/9.5/scripts/runpsql.sh; exit

Davids-MacBook-Pro-2:~ davidblomstrom$ /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit

Server [localhost]: Server [localhost]:

Database [postgres]: Database [postgres]:

Port [5432]: Port [5432]:

Username [postgres]: Username [postgres]:

psql: warning: extra command-line argument "[5432]:" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: warning: extra command-line argument "Database" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: invalid port number: "Port"


Press <return> to continue...CREATE TABLE public.gz_life_mammals2

(

  id            int PRIMARY KEY,

  taxon         text UNIQUE NOT NULL,

  parent        text NOT NULL,

  slug          text,

  name_common   text,

  plural        text,

  extinct       smallint NOT NULL,

  rank          smallint NOT NULL,

  key           smallint NOT NULL

);logout

Saving session...

...copying shared history...

...saving history...truncating history files...

...completed.


[Process completed]


* * * * * 

@ Rob Sargent - I already have two databases, postrgres and geozoo. Geozoo has two tables in it. I was just trying to create another table with the shell (PSQL).

Re: Recursive Arrays 101

From
Adrian Klaver
Date:
On 10/26/2015 05:07 PM, David Blomstrom wrote:
>
>
> On Mon, Oct 26, 2015 at 5:02 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 10/26/2015 04:42 PM, David Blomstrom wrote:
>
>         Last login: Mon Oct 26 16:35:25 on ttys002
>
>         /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit
>
>         Davids-MacBook-Pro-2:~ davidblomstrom$
>         /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit
>
>         Server [localhost]: \l
>
>         Database [postgres]: \dt
>
>         Port [5432]:
>
>         Username [postgres]: Cmd-Spacebar
>
>
>     You are mixing instructions. Do:
>
>     Server [localhost]:
>
>     Database [postgres]:
>
>     Port [5432]:
>
>     Username [postgres]:
>
>     where you just hit Enter after each prompt above. The [parameter]s
>     are default values and I would just accept them for now. Once you
>     get through the prompts it will open a terminal with psql loaded.
>
> Last login: Mon Oct 26 16:48:35 on ttys002
>
> /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit
>
> Davids-MacBook-Pro-2:~ davidblomstrom$
> /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit
>
> Server [localhost]: Server [localhost]:
>
> Database [postgres]: Database [postgres]:
>
> Port [5432]: Port [5432]:
>
> Username [postgres]: Username [postgres]:

So hitting Enter created the second prompt on each line?

>
> psql: warning: extra command-line argument "[5432]:" ignored
>
> psql: warning: extra command-line argument "[postgres]:" ignored
>
> psql: warning: extra command-line argument "Database" ignored
>
> psql: warning: extra command-line argument "[postgres]:" ignored
>
> psql: invalid port number: "Port"

Well this went nowhere.

>
>
> Press <return> to continue...CREATE TABLE public.gz_life_mammals2
>
> (
>
>    id            int PRIMARY KEY,
>
>    taxon         text UNIQUE NOT NULL,
>
>    parent        text NOT NULL,
>
>    slug          text,
>
>    name_common   text,
>
>    plural        text,
>
>    extinct       smallint NOT NULL,
>
>    rank          smallint NOT NULL,
>
>    key           smallint NOT NULL
>
> );logout
>
> Saving session...
>
> ...copying shared history...
>
> ...saving history...truncating history files...
>
> ...completed.
>
>
> [Process completed]
>
>
> * * * * *
>
> @ Rob Sargent - I already have two databases, postrgres and geozoo.
> Geozoo has two tables in it. I was just trying to create another table
> with the shell (PSQL).
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Recursive Arrays 101

From
"David G. Johnston"
Date:
On Mon, Oct 26, 2015 at 8:07 PM, David Blomstrom <david.blomstrom@gmail.com> wrote:

Server [localhost]: Server [localhost]:

Database [postgres]: Database [postgres]:

Port [5432]: Port [5432]:

Username [postgres]: Username [postgres]:

What exactly are you showing us here?

David J.

Re: Recursive Arrays 101

From
David Blomstrom
Date:
I'm just showing you what happened when I typed in \l and hit enter, then typed in \dt and hit enter, etc.

As Adrian Klaver said, this isn't going anywhere. The Seattle PostgreSQL User Group has its monthly meeting in a month or two. I work night shift, so I can't really make it, but if I can drop in at the very beginning meeting, maybe I can leave a note for them. If I can hire someone to set up PostgreSQL for me, create a database and table and publish them online, so it's actually working, and I know how to repeat what they did, then it might work out.

Otherwise, PostgreSQL is obviously too advanced for me. I don't have a clue about what's going on. Thanks for all the tips, though. I got some good general pointers for designing my animals database.

On Mon, Oct 26, 2015 at 5:20 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Mon, Oct 26, 2015 at 8:07 PM, David Blomstrom <david.blomstrom@gmail.com> wrote:

Server [localhost]: Server [localhost]:

Database [postgres]: Database [postgres]:

Port [5432]: Port [5432]:

Username [postgres]: Username [postgres]:

What exactly are you showing us here?

David J.




--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
John R Pierce
Date:
On 10/26/2015 5:20 PM, David G. Johnston wrote:
> What exactly are you showing us here?

he's demonstrating a lack of reading comprehension.   I'm done. thread
on ignore.



--
john r pierce, recycling bits in santa cruz



Re: Recursive Arrays 101

From
Adrian Klaver
Date:
On 10/26/2015 05:27 PM, David Blomstrom wrote:
> I'm just showing you what happened when I typed in \l and hit enter,
> then typed in \dt and hit enter, etc.

To repeat:

Do:

Server [localhost]:

Database [postgres]:

Port [5432]:

Username [postgres]:

Just hit the Enter key after each of the above prompts, do NOT enter any
values.

>
> As Adrian Klaver said, this isn't going anywhere. The Seattle PostgreSQL
> User Group has its monthly meeting in a month or two. I work night
> shift, so I can't really make it, but if I can drop in at the very
> beginning meeting, maybe I can leave a note for them. If I can hire
> someone to set up PostgreSQL for me, create a database and table and
> publish them online, so it's actually working, and I know how to repeat
> what they did, then it might work out.
>
> Otherwise, PostgreSQL is obviously too advanced for me. I don't have a
> clue about what's going on. Thanks for all the tips, though. I got some
> good general pointers for designing my animals database.

The above really has nothing to do with Postgres as you have not
actually got to it yet.

>
> On Mon, Oct 26, 2015 at 5:20 PM, David G. Johnston
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
>
>     On Mon, Oct 26, 2015 at 8:07 PM, David Blomstrom
>     <david.blomstrom@gmail.com <mailto:david.blomstrom@gmail.com>>wrote:
>
>         Server [localhost]: Server [localhost]:
>
>         Database [postgres]: Database [postgres]:
>
>         Port [5432]: Port [5432]:
>
>         Username [postgres]: Username [postgres]:
>
>     What exactly are you showing us here?
>
>     David J.
>
>
>
>
> --
> David Blomstrom
> Writer & Web Designer (Mac, M$ & Linux)
> www.geobop.org <http://www.geobop.org>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Recursive Arrays 101

From
Gavin Flower
Date:
On 27/10/15 13:29, John R Pierce wrote:
> On 10/26/2015 5:20 PM, David G. Johnston wrote:
>> What exactly are you showing us here?
>
> he's demonstrating a lack of reading comprehension.   I'm done. thread
> on ignore.
>
>
>
I think its proof that Apple products rot your brain!



Re: Recursive Arrays 101

From
David Blomstrom
Date:
That's exactly what I've been doing. I just did it again...

Last login: Mon Oct 26 17:53:05 on ttys001

Davids-MacBook-Pro-2:~ davidblomstrom$ /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit

Server [localhost]: Server [localhost

Database [postgres]: Database [postgres]:

Port [5432]: Port [5432]:

Username [postgres]: Username [postgres]:

psql: warning: extra command-line argument "[5432]:" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: warning: extra command-line argument "Database" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: invalid port number: "Port"


Press <return> to continue...


On Mon, Oct 26, 2015 at 5:49 PM, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:
On 27/10/15 13:29, John R Pierce wrote:
On 10/26/2015 5:20 PM, David G. Johnston wrote:
What exactly are you showing us here?

he's demonstrating a lack of reading comprehension.   I'm done. thread on ignore.



I think its proof that Apple products rot your brain!




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
"David G. Johnston"
Date:
On Mon, Oct 26, 2015 at 8:54 PM, David Blomstrom <david.blomstrom@gmail.com> wrote:
That's exactly what I've been doing. I just did it again...

Last login: Mon Oct 26 17:53:05 on ttys001

Davids-MacBook-Pro-2:~ davidblomstrom$ /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit

Server [localhost]: Server [localhost

Database [postgres]: Database [postgres]:

Port [5432]: Port [5432]:

Username [postgres]: Username [postgres]:

psql: warning: extra command-line argument "[5432]:" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: warning: extra command-line argument "Database" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: invalid port number: "Port"


​Then the script you are running is broken and you should run "psql" yourself from a Mac terminal prompt.

David J.
 

Re: Recursive Arrays 101

From
Melvin Davidson
Date:
The law of O/S & databases:
For every Linux / PostgreSQL user, there is and equal an opposite Mac / MySQL user.
However, the latter is completely useless.

On Mon, Oct 26, 2015 at 8:54 PM, David Blomstrom <david.blomstrom@gmail.com> wrote:
That's exactly what I've been doing. I just did it again...

Last login: Mon Oct 26 17:53:05 on ttys001

Davids-MacBook-Pro-2:~ davidblomstrom$ /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit

Server [localhost]: Server [localhost

Database [postgres]: Database [postgres]:

Port [5432]: Port [5432]:

Username [postgres]: Username [postgres]:

psql: warning: extra command-line argument "[5432]:" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: warning: extra command-line argument "Database" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: invalid port number: "Port"


Press <return> to continue...


On Mon, Oct 26, 2015 at 5:49 PM, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:
On 27/10/15 13:29, John R Pierce wrote:
On 10/26/2015 5:20 PM, David G. Johnston wrote:
What exactly are you showing us here?

he's demonstrating a lack of reading comprehension.   I'm done. thread on ignore.



I think its proof that Apple products rot your brain!




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org



--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: Recursive Arrays 101

From
Adrian Klaver
Date:
On 10/26/2015 05:54 PM, David Blomstrom wrote:
> That's exactly what I've been doing. I just did it again...

Hmm, maybe time to try Robs suggestion:

"
Cmd-Spacebar
Terminal

This should find the actual normal terminal.

Click on it."

Edited from his original post-
at the prompt therein: psql --username postgres --host localhost



>
> Last login: Mon Oct 26 17:53:05 on ttys001
>
> Davids-MacBook-Pro-2:~ davidblomstrom$
> /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit
>
> Server [localhost]: Server [localhost
>
> Database [postgres]: Database [postgres]:
>
> Port [5432]: Port [5432]:
>
> Username [postgres]: Username [postgres]:
>
> psql: warning: extra command-line argument "[5432]:" ignored
>
> psql: warning: extra command-line argument "[postgres]:" ignored
>
> psql: warning: extra command-line argument "Database" ignored
>
> psql: warning: extra command-line argument "[postgres]:" ignored
>
> psql: invalid port number: "Port"
>
>
> Press <return> to continue...
>
>
> On Mon, Oct 26, 2015 at 5:49 PM, Gavin Flower
> <GavinFlower@archidevsys.co.nz <mailto:GavinFlower@archidevsys.co.nz>>
> wrote:
>
>     On 27/10/15 13:29, John R Pierce wrote:
>
>         On 10/26/2015 5:20 PM, David G. Johnston wrote:
>
>             What exactly are you showing us here?
>
>
>         he's demonstrating a lack of reading comprehension.   I'm done.
>         thread on ignore.
>
>
>
>     I think its proof that Apple products rot your brain!
>
>
>
>
>     --
>     Sent via pgsql-general mailing list (pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>
> --
> David Blomstrom
> Writer & Web Designer (Mac, M$ & Linux)
> www.geobop.org <http://www.geobop.org>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Recursive Arrays 101

From
David Blomstrom
Date:
Judging from the anti-Mac comments, it sounds like PostgreSQL is kind of a Linux/Microsoft thing. If PostgreSQL isn't compatible with Apple, then that's definitely the end of the line. I used M$ for years and would never go back. I used to be a passionate Linux supporter - largely because I wanted to see it compete with Microsoft - but the Linux community never could understand the concept of "user friendly."

I get far more service from my Mac than I ever got from M$, and I won't waste my time with any software that isn't Mac-compatible.

On Mon, Oct 26, 2015 at 6:01 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
The law of O/S & databases:
For every Linux / PostgreSQL user, there is and equal an opposite Mac / MySQL user.
However, the latter is completely useless.

On Mon, Oct 26, 2015 at 8:54 PM, David Blomstrom <david.blomstrom@gmail.com> wrote:
That's exactly what I've been doing. I just did it again...

Last login: Mon Oct 26 17:53:05 on ttys001

Davids-MacBook-Pro-2:~ davidblomstrom$ /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit

Server [localhost]: Server [localhost

Database [postgres]: Database [postgres]:

Port [5432]: Port [5432]:

Username [postgres]: Username [postgres]:

psql: warning: extra command-line argument "[5432]:" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: warning: extra command-line argument "Database" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: invalid port number: "Port"


Press <return> to continue...


On Mon, Oct 26, 2015 at 5:49 PM, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:
On 27/10/15 13:29, John R Pierce wrote:
On 10/26/2015 5:20 PM, David G. Johnston wrote:
What exactly are you showing us here?

he's demonstrating a lack of reading comprehension.   I'm done. thread on ignore.



I think its proof that Apple products rot your brain!




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org



--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.




--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
Rob Sargent
Date:
You will do what you need to do but please do not claim that pg is not Mac compatible. Many on this thread are regular Mac/pg users. We all thought we were doing you a favour in trying to free you from GUI tools. For us they are restraining. For you they may be liberating. Stick with pgAdmin and ask questions about it. There may even be a forum dedicated to it. 

Someday we can revisit "power tools"

On Oct 26, 2015, at 7:08 PM, David Blomstrom <david.blomstrom@gmail.com> wrote:

Judging from the anti-Mac comments, it sounds like PostgreSQL is kind of a Linux/Microsoft thing. If PostgreSQL isn't compatible with Apple, then that's definitely the end of the line. I used M$ for years and would never go back. I used to be a passionate Linux supporter - largely because I wanted to see it compete with Microsoft - but the Linux community never could understand the concept of "user friendly."

I get far more service from my Mac than I ever got from M$, and I won't waste my time with any software that isn't Mac-compatible.

On Mon, Oct 26, 2015 at 6:01 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
The law of O/S & databases:
For every Linux / PostgreSQL user, there is and equal an opposite Mac / MySQL user.
However, the latter is completely useless.

On Mon, Oct 26, 2015 at 8:54 PM, David Blomstrom <david.blomstrom@gmail.com> wrote:
That's exactly what I've been doing. I just did it again...

Last login: Mon Oct 26 17:53:05 on ttys001

Davids-MacBook-Pro-2:~ davidblomstrom$ /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit

Server [localhost]: Server [localhost

Database [postgres]: Database [postgres]:

Port [5432]: Port [5432]:

Username [postgres]: Username [postgres]:

psql: warning: extra command-line argument "[5432]:" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: warning: extra command-line argument "Database" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: invalid port number: "Port"


Press <return> to continue...


On Mon, Oct 26, 2015 at 5:49 PM, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:
On 27/10/15 13:29, John R Pierce wrote:
On 10/26/2015 5:20 PM, David G. Johnston wrote:
What exactly are you showing us here?

he's demonstrating a lack of reading comprehension.   I'm done. thread on ignore.



I think its proof that Apple products rot your brain!




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org



--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.




--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
David Blomstrom
Date:
Doesn't this thread demonstrate the advantage of GUI's??? I created a database and table with pgAdmin III with no help at all.

I then got a flurry of well-intentioned tips about the command-line tool. I tried all of them. I tried them twice. I tried them upside down and backwards. People then began complaining that I can't follow directions. Like how many different ways are there to type in a command and hit enter???

I think it was obvious a couple dozen posts ago that there's something wrong with my PostgreSQL installation or with the command-line tool.

On Mon, Oct 26, 2015 at 6:43 PM, Rob Sargent <robjsargent@gmail.com> wrote:
You will do what you need to do but please do not claim that pg is not Mac compatible. Many on this thread are regular Mac/pg users. We all thought we were doing you a favour in trying to free you from GUI tools. For us they are restraining. For you they may be liberating. Stick with pgAdmin and ask questions about it. There may even be a forum dedicated to it. 

Someday we can revisit "power tools"

On Oct 26, 2015, at 7:08 PM, David Blomstrom <david.blomstrom@gmail.com> wrote:

Judging from the anti-Mac comments, it sounds like PostgreSQL is kind of a Linux/Microsoft thing. If PostgreSQL isn't compatible with Apple, then that's definitely the end of the line. I used M$ for years and would never go back. I used to be a passionate Linux supporter - largely because I wanted to see it compete with Microsoft - but the Linux community never could understand the concept of "user friendly."

I get far more service from my Mac than I ever got from M$, and I won't waste my time with any software that isn't Mac-compatible.

On Mon, Oct 26, 2015 at 6:01 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
The law of O/S & databases:
For every Linux / PostgreSQL user, there is and equal an opposite Mac / MySQL user.
However, the latter is completely useless.

On Mon, Oct 26, 2015 at 8:54 PM, David Blomstrom <david.blomstrom@gmail.com> wrote:
That's exactly what I've been doing. I just did it again...

Last login: Mon Oct 26 17:53:05 on ttys001

Davids-MacBook-Pro-2:~ davidblomstrom$ /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit

Server [localhost]: Server [localhost

Database [postgres]: Database [postgres]:

Port [5432]: Port [5432]:

Username [postgres]: Username [postgres]:

psql: warning: extra command-line argument "[5432]:" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: warning: extra command-line argument "Database" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: invalid port number: "Port"


Press <return> to continue...


On Mon, Oct 26, 2015 at 5:49 PM, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:
On 27/10/15 13:29, John R Pierce wrote:
On 10/26/2015 5:20 PM, David G. Johnston wrote:
What exactly are you showing us here?

he's demonstrating a lack of reading comprehension.   I'm done. thread on ignore.



I think its proof that Apple products rot your brain!




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org



--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.




--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
Rob Sargent
Date:


On Oct 26, 2015, at 7:48 PM, David Blomstrom <david.blomstrom@gmail.com> wrote:

Doesn't this thread demonstrate the advantage of GUI's??? I created a database and table with pgAdmin III with no help at all.

I then got a flurry of well-intentioned tips about the command-line tool. I tried all of them. I tried them twice. I tried them upside down and backwards. People then began complaining that I can't follow directions. Like how many different ways are there to type in a command and hit enter???

I think it was obvious a couple dozen posts ago that there's something wrong with my PostgreSQL installation or with the command-line tool.

I'm in lined to agree that your icon-generating-she'll thingy is suspect. Did you ever try the basic Terminal?  That puts you at the most basic command line talking to the operating system.   psql from there gets you to a database shell talking to your server.
You can indeed do a lot of what you want from pgadmin.  I'm mostly to blame for this thread so if you would like to take this up with me privately you're welcome to do so. 

On Mon, Oct 26, 2015 at 6:43 PM, Rob Sargent <robjsargent@gmail.com> wrote:
You will do what you need to do but please do not claim that pg is not Mac compatible. Many on this thread are regular Mac/pg users. We all thought we were doing you a favour in trying to free you from GUI tools. For us they are restraining. For you they may be liberating. Stick with pgAdmin and ask questions about it. There may even be a forum dedicated to it. 

Someday we can revisit "power tools"

On Oct 26, 2015, at 7:08 PM, David Blomstrom <david.blomstrom@gmail.com> wrote:

Judging from the anti-Mac comments, it sounds like PostgreSQL is kind of a Linux/Microsoft thing. If PostgreSQL isn't compatible with Apple, then that's definitely the end of the line. I used M$ for years and would never go back. I used to be a passionate Linux supporter - largely because I wanted to see it compete with Microsoft - but the Linux community never could understand the concept of "user friendly."

I get far more service from my Mac than I ever got from M$, and I won't waste my time with any software that isn't Mac-compatible.

On Mon, Oct 26, 2015 at 6:01 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
The law of O/S & databases:
For every Linux / PostgreSQL user, there is and equal an opposite Mac / MySQL user.
However, the latter is completely useless.

On Mon, Oct 26, 2015 at 8:54 PM, David Blomstrom <david.blomstrom@gmail.com> wrote:
That's exactly what I've been doing. I just did it again...

Last login: Mon Oct 26 17:53:05 on ttys001

Davids-MacBook-Pro-2:~ davidblomstrom$ /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit

Server [localhost]: Server [localhost

Database [postgres]: Database [postgres]:

Port [5432]: Port [5432]:

Username [postgres]: Username [postgres]:

psql: warning: extra command-line argument "[5432]:" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: warning: extra command-line argument "Database" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: invalid port number: "Port"


Press <return> to continue...


On Mon, Oct 26, 2015 at 5:49 PM, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:
On 27/10/15 13:29, John R Pierce wrote:
On 10/26/2015 5:20 PM, David G. Johnston wrote:
What exactly are you showing us here?

he's demonstrating a lack of reading comprehension.   I'm done. thread on ignore.



I think its proof that Apple products rot your brain!




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org



--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.




--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
Adrian Klaver
Date:
On 10/26/2015 06:08 PM, David Blomstrom wrote:
> Judging from the anti-Mac comments, it sounds like PostgreSQL is kind of
> a Linux/Microsoft thing. If PostgreSQL isn't compatible with Apple, then

That is definitely not true. OS X under the hood comes from the same
basic lineage as Linux, by way of BSD. It is entirely compatible.

> that's definitely the end of the line. I used M$ for years and would
> never go back. I used to be a passionate Linux supporter - largely
> because I wanted to see it compete with Microsoft - but the Linux
> community never could understand the concept of "user friendly."

Given that I deal with Windows and Linux on a regular basis I would say
different, but that is a different argument.

>
> I get far more service from my Mac than I ever got from M$, and I won't
> waste my time with any software that isn't Mac-compatible.

See above.
>
> On Mon, Oct 26, 2015 at 6:01 PM, Melvin Davidson <melvin6925@gmail.com
> <mailto:melvin6925@gmail.com>> wrote:
>
>     The law of O/S & databases:
>     For every Linux / PostgreSQL user, there is and equal an opposite
>     Mac / MySQL user.
>     However, the latter is completely useless.
>
>     On Mon, Oct 26, 2015 at 8:54 PM, David Blomstrom
>     <david.blomstrom@gmail.com <mailto:david.blomstrom@gmail.com>> wrote:
>
>         That's exactly what I've been doing. I just did it again...
>
>         Last login: Mon Oct 26 17:53:05 on ttys001
>
>         Davids-MacBook-Pro-2:~ davidblomstrom$
>         /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit
>
>         Server [localhost]: Server [localhost
>
>         Database [postgres]: Database [postgres]:
>
>         Port [5432]: Port [5432]:
>
>         Username [postgres]: Username [postgres]:
>
>         psql: warning: extra command-line argument "[5432]:" ignored
>
>         psql: warning: extra command-line argument "[postgres]:" ignored
>
>         psql: warning: extra command-line argument "Database" ignored
>
>         psql: warning: extra command-line argument "[postgres]:" ignored
>
>         psql: invalid port number: "Port"
>
>
>         Press <return> to continue...
>
>
>         On Mon, Oct 26, 2015 at 5:49 PM, Gavin Flower
>         <GavinFlower@archidevsys.co.nz
>         <mailto:GavinFlower@archidevsys.co.nz>> wrote:
>
>             On 27/10/15 13:29, John R Pierce wrote:
>
>                 On 10/26/2015 5:20 PM, David G. Johnston wrote:
>
>                     What exactly are you showing us here?
>
>
>                 he's demonstrating a lack of reading comprehension.
>                   I'm done. thread on ignore.
>
>
>
>             I think its proof that Apple products rot your brain!
>
>
>
>
>             --
>             Sent via pgsql-general mailing list
>             (pgsql-general@postgresql.org
>             <mailto:pgsql-general@postgresql.org>)
>             To make changes to your subscription:
>             http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>
>         --
>         David Blomstrom
>         Writer & Web Designer (Mac, M$ & Linux)
>         www.geobop.org <http://www.geobop.org>
>
>
>
>
>     --
>     *Melvin Davidson*
>     I reserve the right to fantasize.  Whether or not you
>     wish to share my fantasy is entirely up to you.
>
>
>
>
> --
> David Blomstrom
> Writer & Web Designer (Mac, M$ & Linux)
> www.geobop.org <http://www.geobop.org>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Recursive Arrays 101

From
Adrian Klaver
Date:
On 10/26/2015 06:48 PM, David Blomstrom wrote:
> Doesn't this thread demonstrate the advantage of GUI's??? I created a
> database and table with pgAdmin III with no help at all.

At this point I would say sticking with pgAdmin is the best option.

>
> I then got a flurry of well-intentioned tips about the command-line
> tool. I tried all of them. I tried them twice. I tried them upside down
> and backwards. People then began complaining that I can't follow
> directions. Like how many different ways are there to type in a command
> and hit enter???

Go back through the thread and look at your responses to specific
questions and with an open mind ask whether they answered the question?

>
> I think it was obvious a couple dozen posts ago that there's something
> wrong with my PostgreSQL installation or with the command-line tool.

No it was not obvious, otherwise we would not have asked for more
information.

>
> On Mon, Oct 26, 2015 at 6:43 PM, Rob Sargent <robjsargent@gmail.com



--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Recursive Arrays 101

From
"David G. Johnston"
Date:
On Mon, Oct 26, 2015 at 10:14 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/26/2015 06:48 PM, David Blomstrom wrote:
Doesn't this thread demonstrate the advantage of GUI's??? I created a
database and table with pgAdmin III with no help at all.

At this point I would say sticking with pgAdmin is the best option.


​They both have their places.  It is usually quite difficult to automate and version control the manual work that goes into using command line tools.​


I then got a flurry of well-intentioned tips about the command-line
tool. I tried all of them. I tried them twice. I tried them upside down
and backwards. People then began complaining that I can't follow
directions. Like how many different ways are there to type in a command
and hit enter???

Go back through the thread and look at your responses to specific questions and with an open mind ask whether they answered the question?


I think it was obvious a couple dozen posts ago that there's something
wrong with my PostgreSQL installation or with the command-line tool.

No it was not obvious, otherwise we would not have asked for more information.

​Most of us assumed some form of user-error as opposed to malfunctioning software...it usually takes detail and explanations to rule that out.  When you are using lesser-used (and unofficial) tools the relevant experience to be found on the official lists is likewise hampered.

​Without going through the entire thread I would have to say that this whole dynamic is a confluence of experienced users, novice users, and a text-medium 
​gone wrong.​  Or it was just a bad day.  In any case as the requester taking a step back and framing things up will help others to provide better assistance.

David J.


 

Re: Recursive Arrays 101

From
John R Pierce
Date:
On 10/26/2015 7:44 PM, David G. Johnston wrote:
> ​They both have their places.  It is usually quite difficult to
> automate and version control the manual work that goes into using
> command line tools.​

I hope you mean, its difficult to automate and version control
clickity-clicky work that goes into using GUI tools

automating shell scripts is trivial.   putting said shell scripts into
version control is also trivial.



--
john r pierce, recycling bits in santa cruz



Re: Recursive Arrays 101

From
"David G. Johnston"
Date:
On Monday, October 26, 2015, John R Pierce <pierce@hogranch.com> wrote:
On 10/26/2015 7:44 PM, David G. Johnston wrote:
​They both have their places.  It is usually quite difficult to automate and version control the manual work that goes into using command line tools.​

I hope you mean, its difficult to automate and version control clickity-clicky work that goes into using GUI tools

automating shell scripts is trivial.   putting said shell scripts into version control is also trivial.


Yes, that is a typo on my part. 

Re: Recursive Arrays 101

From
Achilleas Mantzios
Date:
Sorry for being kind of late to the party (I was in 2015.PgConf.EU !!), and not having read
most of the replies, what we have been successfully doing for this problem for our app
is do it this way :
parents int[] -- where parents stores the path from the node to the root of the tree
and then have those indexes :
btree (first(parents))
btree (level(parents)) -- length
btree (last(parents))
gin (parents gin__int_ops) -- the most important

This has been described as "genealogical tree" approach, and works very good, IMHO much better
than nested sets.

On 27/10/2015 14:46, David G. Johnston wrote:
On Monday, October 26, 2015, John R Pierce <pierce@hogranch.com> wrote:
On 10/26/2015 7:44 PM, David G. Johnston wrote:
​They both have their places.  It is usually quite difficult to automate and version control the manual work that goes into using command line tools.​

I hope you mean, its difficult to automate and version control clickity-clicky work that goes into using GUI tools

automating shell scripts is trivial.   putting said shell scripts into version control is also trivial.


Yes, that is a typo on my part. 


-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Recursive Arrays 101

From
Rob Sargent
Date:
On 11/04/2015 03:03 AM, Achilleas Mantzios wrote:
Sorry for being kind of late to the party (I was in 2015.PgConf.EU !!), and not having read
most of the replies, what we have been successfully doing for this problem for our app
is do it this way :
parents int[] -- where parents stores the path from the node to the root of the tree
and then have those indexes :
btree (first(parents))
btree (level(parents)) -- length
btree (last(parents))
gin (parents gin__int_ops) -- the most important

This has been described as "genealogical tree" approach, and works very good, IMHO much better
than nested sets.

Is there a more complete description of this approach available?  By the title one might assume could be applied to populations as opposed to phylogeny (the OP's use case).  Does it deal with consanguinity?  Does it perform well going "up" the tree (which is of course branched at every level)?

Re: Recursive Arrays 101

From
David Blomstrom
Date:
Thanks for that tip, Achilleas.

On Wed, Nov 4, 2015 at 7:53 AM, Rob Sargent <robjsargent@gmail.com> wrote:
On 11/04/2015 03:03 AM, Achilleas Mantzios wrote:
Sorry for being kind of late to the party (I was in 2015.PgConf.EU !!), and not having read
most of the replies, what we have been successfully doing for this problem for our app
is do it this way :
parents int[] -- where parents stores the path from the node to the root of the tree
and then have those indexes :
btree (first(parents))
btree (level(parents)) -- length
btree (last(parents))
gin (parents gin__int_ops) -- the most important

This has been described as "genealogical tree" approach, and works very good, IMHO much better
than nested sets.

Is there a more complete description of this approach available?  By the title one might assume could be applied to populations as opposed to phylogeny (the OP's use case).  Does it deal with consanguinity?  Does it perform well going "up" the tree (which is of course branched at every level)?



--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: Recursive Arrays 101

From
Achilleas Mantzios
Date:
On 04/11/2015 17:53, Rob Sargent wrote:
On 11/04/2015 03:03 AM, Achilleas Mantzios wrote:
Sorry for being kind of late to the party (I was in 2015.PgConf.EU !!), and not having read
most of the replies, what we have been successfully doing for this problem for our app
is do it this way :
parents int[] -- where parents stores the path from the node to the root of the tree
and then have those indexes :
btree (first(parents))
btree (level(parents)) -- length
btree (last(parents))
gin (parents gin__int_ops) -- the most important

This has been described as "genealogical tree" approach, and works very good, IMHO much better
than nested sets.

Is there a more complete description of this approach available?  By the title one might assume could be applied to populations as opposed to phylogeny (the OP's use case).  Does it deal with consanguinity?  Does it perform well going "up" the tree (which is of course branched at every level)?

From here https://en.wikipedia.org/wiki/Phylogenetic_tree I assume that phylogenetic trees are normal
trees, and I see no reason why not be modeled with the genealogical approach described. The earliest paper
I based my work on was :

https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&ved=0CCUQFjABahUKEwiR6auUlvnIAhXGvhQKHVyDA-s&url=https%3A%2F%2Fdownload.samba.org%2Fpub%2Funpacked%2Fldb%2Fldb_sqlite3%2Ftrees.ps&usg=AFQjCNEktJsibP435MBki5cdGmO_CzKmwg&sig2=I9yC_tpyeWrEueDJTXbyAA&bvm=bv.106674449,d.d24&cad=rja

Finding the root is O(1). Going "up" the tree or finding common ancestry is reduced to the problem
of finding overlap/intersections/contains/contained between postgresql arrays.

The indexes, functions and operators provided by contrib/intarray were a basic element for the success of this
approach.


-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Recursive Arrays 101

From
Rob Sargent
Date:
On 11/05/2015 04:56 AM, Achilleas Mantzios wrote:
On 04/11/2015 17:53, Rob Sargent wrote:
On 11/04/2015 03:03 AM, Achilleas Mantzios wrote:
Sorry for being kind of late to the party (I was in 2015.PgConf.EU !!), and not having read
most of the replies, what we have been successfully doing for this problem for our app
is do it this way :
parents int[] -- where parents stores the path from the node to the root of the tree
and then have those indexes :
btree (first(parents))
btree (level(parents)) -- length
btree (last(parents))
gin (parents gin__int_ops) -- the most important

This has been described as "genealogical tree" approach, and works very good, IMHO much better
than nested sets.

Is there a more complete description of this approach available?  By the title one might assume could be applied to populations as opposed to phylogeny (the OP's use case).  Does it deal with consanguinity?  Does it perform well going "up" the tree (which is of course branched at every level)?

From here https://en.wikipedia.org/wiki/Phylogenetic_tree I assume that phylogenetic trees are normal
trees, and I see no reason why not be modeled with the genealogical approach described. The earliest paper
I based my work on was :

https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&ved=0CCUQFjABahUKEwiR6auUlvnIAhXGvhQKHVyDA-s&url=https%3A%2F%2Fdownload.samba.org%2Fpub%2Funpacked%2Fldb%2Fldb_sqlite3%2Ftrees.ps&usg=AFQjCNEktJsibP435MBki5cdGmO_CzKmwg&sig2=I9yC_tpyeWrEueDJTXbyAA&bvm=bv.106674449,d.d24&cad=rja

Finding the root is O(1). Going "up" the tree or finding common ancestry is reduced to the problem
of finding overlap/intersections/contains/contained between postgresql arrays.

The indexes, functions and operators provided by contrib/intarray were a basic element for the success of this
approach.

Going "up" a genealogy to me means getting two parents, four grandparents, 8 great grandparents etc.  On a good day, at least when there are no loops.  This isn't, to my understanding, how phylogeny works (but my genetics degree was thirty year ago) so perhaps I'm still confused by the titles used.  And certainly not to say that your approach isn't what the OP really needs!

 

Re: Recursive Arrays 101

From
Gavin Flower
Date:
On 06/11/15 04:33, Rob Sargent wrote:
> On 11/05/2015 04:56 AM, Achilleas Mantzios wrote:
>> On 04/11/2015 17:53, Rob Sargent wrote:
>>> On 11/04/2015 03:03 AM, Achilleas Mantzios wrote:
>>>> Sorry for being kind of late to the party (I was in 2015.PgConf.EU
>>>> !!), and not having read
>>>> most of the replies, what we have been successfully doing for this
>>>> problem for our app
>>>> is do it this way :
>>>> parents int[] -- where parents stores the path from the node to the
>>>> root of the tree
>>>> and then have those indexes :
>>>> btree (first(parents))
>>>> btree (level(parents)) -- length
>>>> btree (last(parents))
>>>> gin (parents gin__int_ops) -- the most important
>>>>
>>>> This has been described as "genealogical tree" approach, and works
>>>> very good, IMHO much better
>>>> than nested sets.
>>>>
>>> Is there a more complete description of this approach available?  By
>>> the title one might assume could be applied to populations as
>>> opposed to phylogeny (the OP's use case).  Does it deal with
>>> consanguinity?  Does it perform well going "up" the tree (which is
>>> of course branched at every level)?
>>
>> From here https://en.wikipedia.org/wiki/Phylogenetic_tree I assume
>> that phylogenetic trees are normal
>> trees, and I see no reason why not be modeled with the genealogical
>> approach described. The earliest paper
>> I based my work on was :
>>
https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&ved=0CCUQFjABahUKEwiR6auUlvnIAhXGvhQKHVyDA-s&url=https%3A%2F%2Fdownload.samba.org%2Fpub%2Funpacked%2Fldb%2Fldb_sqlite3%2Ftrees.ps&usg=AFQjCNEktJsibP435MBki5cdGmO_CzKmwg&sig2=I9yC_tpyeWrEueDJTXbyAA&bvm=bv.106674449,d.d24&cad=rja
>>
>> Finding the root is O(1). Going "up" the tree or finding common
>> ancestry is reduced to the problem
>> of finding overlap/intersections/contains/contained between
>> postgresql arrays.
>>
>> The indexes, functions and operators provided by contrib/intarray
>> were a basic element for the success of this
>> approach.
>>
> Going "up" a genealogy to me means getting two parents, four
> grandparents, 8 great grandparents etc.  On a good day, at least when
> there are no loops.  This isn't, to my understanding, how phylogeny
> works (but my genetics degree was thirty year ago) so perhaps I'm
> still confused by the titles used.  And certainly not to say that your
> approach isn't what the OP really needs!
>
>
You're actually going 'DOWN' the tree, in terms of how trees are used in
computer science & graph theory!

See http://www.mathcove.net/petersen/lessons/get-lesson?les=32


Cheers,
Gavin


Re: Recursive Arrays 101

From
Rob Sargent
Date:
On 11/05/2015 11:08 AM, Gavin Flower wrote:
> On 06/11/15 04:33, Rob Sargent wrote:
>> On 11/05/2015 04:56 AM, Achilleas Mantzios wrote:
>>> On 04/11/2015 17:53, Rob Sargent wrote:
>>>> On 11/04/2015 03:03 AM, Achilleas Mantzios wrote:
>>>>> Sorry for being kind of late to the party (I was in 2015.PgConf.EU
>>>>> !!), and not having read
>>>>> most of the replies, what we have been successfully doing for this
>>>>> problem for our app
>>>>> is do it this way :
>>>>> parents int[] -- where parents stores the path from the node to
>>>>> the root of the tree
>>>>> and then have those indexes :
>>>>> btree (first(parents))
>>>>> btree (level(parents)) -- length
>>>>> btree (last(parents))
>>>>> gin (parents gin__int_ops) -- the most important
>>>>>
>>>>> This has been described as "genealogical tree" approach, and works
>>>>> very good, IMHO much better
>>>>> than nested sets.
>>>>>
>>>> Is there a more complete description of this approach available?
>>>> By the title one might assume could be applied to populations as
>>>> opposed to phylogeny (the OP's use case). Does it deal with
>>>> consanguinity?  Does it perform well going "up" the tree (which is
>>>> of course branched at every level)?
>>>
>>> From here https://en.wikipedia.org/wiki/Phylogenetic_tree I assume
>>> that phylogenetic trees are normal
>>> trees, and I see no reason why not be modeled with the genealogical
>>> approach described. The earliest paper
>>> I based my work on was :
>>>
https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&ved=0CCUQFjABahUKEwiR6auUlvnIAhXGvhQKHVyDA-s&url=https%3A%2F%2Fdownload.samba.org%2Fpub%2Funpacked%2Fldb%2Fldb_sqlite3%2Ftrees.ps&usg=AFQjCNEktJsibP435MBki5cdGmO_CzKmwg&sig2=I9yC_tpyeWrEueDJTXbyAA&bvm=bv.106674449,d.d24&cad=rja

>>>
>>>
>>> Finding the root is O(1). Going "up" the tree or finding common
>>> ancestry is reduced to the problem
>>> of finding overlap/intersections/contains/contained between
>>> postgresql arrays.
>>>
>>> The indexes, functions and operators provided by contrib/intarray
>>> were a basic element for the success of this
>>> approach.
>>>
>> Going "up" a genealogy to me means getting two parents, four
>> grandparents, 8 great grandparents etc.  On a good day, at least when
>> there are no loops.  This isn't, to my understanding, how phylogeny
>> works (but my genetics degree was thirty year ago) so perhaps I'm
>> still confused by the titles used.  And certainly not to say that
>> your approach isn't what the OP really needs!
>>
>>
> You're actually going 'DOWN' the tree, in terms of how trees are used
> in computer science & graph theory!
>
> See http://www.mathcove.net/petersen/lessons/get-lesson?les=32
>
>
> Cheers,
> Gavin
>
>
Fine.  Be that way :)  Still the question of loops/consanguinity?