Thread: Recursive Arrays 101

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

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

> 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.

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

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

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

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>

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

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?

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.

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

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.

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

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

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.
>>
>>
>
>

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

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.
>

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

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

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.

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.

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

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

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

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

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

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

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!

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

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

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!

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

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

Oops, let me try it again...

Shell1

Server [localhost]: \l

Database [postgres]: 

-- 
john r pierce, recycling bits in santa cruz

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!

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

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

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

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

No, I'm on a Mac running OS X El Capitan.

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

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

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

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

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

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!

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

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

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

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

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

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.

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

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

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

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.

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

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?