Re: [GENERAL] Recursive Arrays 101

[Achilleas Mantzios]

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=j==s=web=2=0CCUQFjABahUKEwiR6auUlvnIAhXGvhQKHVyDA-s=https%3A%2F%2Fdownload.samba.org%2Fpub%2Funpacked%2Fldb%2Fldb_sqlite3%2Ftrees.ps=AFQjCNEktJsibP435MBki5cdGmO_CzKmwg=I9yC_tpyeWrEueDJTXbyAA=bv.106674449,d.d24=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: [GENERAL] Recursive Arrays 101

[Rob Sargent]

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=j==s=web=2=0CCUQFjABahUKEwiR6auUlvnIAhXGvhQKHVyDA-s=https%3A%2F%2Fdownload.samba.org%2Fpub%2Funpacked%2Fldb%2Fldb_sqlite3%2Ftrees.ps=AFQjCNEktJsibP435MBki5cdGmO_CzKmwg=I9yC_tpyeWrEueDJTXbyAA=bv.106674449,d.d24=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?





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

Re: [GENERAL] Recursive Arrays 101

[Gavin Flower]

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=j==s=web=2=0CCUQFjABahUKEwiR6auUlvnIAhXGvhQKHVyDA-s=https%3A%2F%2Fdownload.samba.org%2Fpub%2Funpacked%2Fldb%2Fldb_sqlite3%2Ftrees.ps=AFQjCNEktJsibP435MBki5cdGmO_CzKmwg=I9yC_tpyeWrEueDJTXbyAA=bv.106674449,d.d24=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


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

Re: [GENERAL] Recursive Arrays 101

[Rob Sargent]

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=j==s=web=2=0CCUQFjABahUKEwiR6auUlvnIAhXGvhQKHVyDA-s=https%3A%2F%2Fdownload.samba.org%2Fpub%2Funpacked%2Fldb%2Fldb_sqlite3%2Ftrees.ps=AFQjCNEktJsibP435MBki5cdGmO_CzKmwg=I9yC_tpyeWrEueDJTXbyAA=bv.106674449,d.d24=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: [GENERAL] Recursive Arrays 101

[David Blomstrom]

Thanks for that tip, Achilleas.

On Wed, Nov 4, 2015 at 7:53 AM, 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)?
>



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

Re: [GENERAL] Recursive Arrays 101

[Rob Sargent]

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: [GENERAL] Recursive Arrays 101

[Achilleas Mantzios]

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 > 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: [GENERAL] Recursive Arrays 101

[David G. Johnston]

On Monday, October 26, 2015, John R Pierce  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: [GENERAL] Recursive Arrays 101

[Gavin Flower]

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 
> 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.kla...@aklaver.com 




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




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

Re: [GENERAL] Recursive Arrays 101

[Gavin Flower]

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



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

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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



--
Adrian Klaver
adrian.kla...@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

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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

Re: [GENERAL] Recursive Arrays 101

[John R Pierce]

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

Re: [GENERAL] Recursive Arrays 101

[John R Pierce]

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



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

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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 
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.blomst...@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  to continue...
>>
>> On Mon, Oct 26, 2015 at 5:49 PM, Gavin Flower <
>> gavinflo...@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: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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 
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 = ''
>> 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_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.kla...@aklaver.com
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> 

Re: [GENERAL] Recursive Arrays 101

[Rob Sargent]

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 > 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
' 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: [GENERAL] Recursive Arrays 101

[Gavin Flower]

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 - Stellers jay
Plural - Stellers 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




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

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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



--
Adrian Klaver
adrian.kla...@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

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

 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: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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  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 - Stellers jay
>> Plural - Stellers 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: [GENERAL] Recursive Arrays 101

[Rob Sargent]

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


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






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

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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  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 ' 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: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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

Re: [GENERAL] Recursive Arrays 101

[Gavin Flower]

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
(
  idint PRIMARY KEY,
  taxon text UNIQUE NOT NULL,
  parenttext NOT NULL,
  slug  text,
  name_common   text,
  pluraltext,
  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



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

Re: [GENERAL] Recursive Arrays 101

[Gavin Flower]

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



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

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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  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: [GENERAL] Recursive Arrays 101

[Gavin Flower]

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
(
  idint PRIMARY KEY,
  taxon text UNIQUE NOT NULL,
  parenttext NOT NULL,
  slug  text,
  name_common   text,
  pluraltext,
  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!



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

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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





--
Adrian Klaver
adrian.kla...@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

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

 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: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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  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: [GENERAL] Recursive Arrays 101

[David G. Johnston]

On Mon, Oct 26, 2015 at 8:54 PM, David Blomstrom 
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: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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 - Stellers jay
Plural - Stellers 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 
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  > 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 

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

 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: [GENERAL] Recursive Arrays 101

[Rob Sargent]

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 
> 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: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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  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  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: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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+-mjkeyrot69um3ra...@mail.gmail.com


--
Adrian Klaver
adrian.kla...@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

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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  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: [GENERAL] Recursive Arrays 101

[Gavin Flower]

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


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

Re: [GENERAL] Recursive Arrays 101

[David G. Johnston]

On Mon, Oct 26, 2015 at 7:36 PM, David Blomstrom 
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: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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

Re: [GENERAL] Recursive Arrays 101

[Gavin Flower]

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

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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

On Mon, Oct 26, 2015 at 8:07 PM, David Blomstrom
>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 



--
Adrian Klaver
adrian.kla...@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

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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 
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+-mjkeyrot69um3ra...@mail.gmail.com
>
>
> --
> Adrian Klaver
> adrian.kla...@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: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

On 10/26/2015 04:21 PM, David Blomstrom wrote:



On Mon, Oct 26, 2015 at 4:19 PM, Adrian Klaver
> 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.kla...@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.kla...@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

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

On Mon, Oct 26, 2015 at 5:02 PM, Adrian Klaver 
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  to continue...CREATE TABLE public.gz_life_mammals2

(

  idint PRIMARY KEY,

  taxon text UNIQUE NOT NULL,

  parenttext NOT NULL,

  slug  text,

  name_common   text,

  pluraltext,

  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: [GENERAL] Recursive Arrays 101

[Melvin Davidson]

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 
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  to continue...
>
> On Mon, Oct 26, 2015 at 5:49 PM, Gavin Flower <
> gavinflo...@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: [GENERAL] Recursive Arrays 101

[Gavin Flower]

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!



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

Re: [GENERAL] Recursive Arrays 101

[Gavin Flower]

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


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


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

Re: [GENERAL] Recursive Arrays 101

[John R Pierce]

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



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

Re: [GENERAL] Recursive Arrays 101

[Rob Sargent]

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 
\dt will list tables

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

On 10/26/2015 05:07 PM, David Blomstrom wrote:



On Mon, Oct 26, 2015 at 5:02 PM, Adrian Klaver
> 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  to continue...CREATE TABLE public.gz_life_mammals2

(

   idint PRIMARY KEY,

   taxon text UNIQUE NOT NULL,

   parenttext NOT NULL,

   slug  text,

   name_common   text,

   pluraltext,

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

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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.johns...@gmail.com> wrote:

> On Mon, Oct 26, 2015 at 8:07 PM, David Blomstrom <
> david.blomst...@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: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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

On Mon, Oct 26, 2015 at 5:49 PM, Gavin Flower  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: [GENERAL] Recursive Arrays 101

[David Blomstrom]

 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
(
  idint PRIMARY KEY,
  taxon text UNIQUE NOT NULL,
  parenttext NOT NULL,
  slug  text,
  name_common   text,
  pluraltext,
  extinct   smallint NOT NULL,
  rank  smallint NOT NULL,
  key   smallint NOT NULL
);

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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
(
   idint PRIMARY KEY,
   taxon text UNIQUE NOT NULL,
   parenttext NOT NULL,
   slug  text,
   name_common   text,
   pluraltext,
   extinct   smallint NOT NULL,
   rank  smallint NOT NULL,
   key   smallint NOT NULL
);



--
Adrian Klaver
adrian.kla...@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

Re: [GENERAL] Recursive Arrays 101

[Gavin Flower]

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
(
  idint PRIMARY KEY,
  taxon text UNIQUE NOT NULL,
  parenttext NOT NULL,
  slug  text,
  name_common   text,
  pluraltext,
  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


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

Re: [GENERAL] Recursive Arrays 101

[John R Pierce]

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: [GENERAL] Recursive Arrays 101

[David G. Johnston]

On Mon, Oct 26, 2015 at 7:13 PM, David Blomstrom 
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: [GENERAL] Recursive Arrays 101

[David Blomstrom]

On Mon, Oct 26, 2015 at 4:19 PM, Adrian Klaver 
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.kla...@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: [GENERAL] Recursive Arrays 101

[Gavin Flower]

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



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

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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

Re: [GENERAL] Recursive Arrays 101

[David G. Johnston]

On Mon, Oct 26, 2015 at 8:07 PM, David Blomstrom 
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: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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


On Mon, Oct 26, 2015 at 5:49 PM, Gavin Flower
>
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 



--
Adrian Klaver
adrian.kla...@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

Re: [GENERAL] Recursive Arrays 101

[Rob Sargent]

> On Oct 26, 2015, at 7: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.
> 
> 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  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  
>>> 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  
 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 
>  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  to continue...
> 
> 
>> On Mon, Oct 26, 2015 at 5:49 PM, Gavin Flower 
>>  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: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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 

Re: [GENERAL] Recursive Arrays 101

[John R Pierce]

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



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

Re: [GENERAL] Recursive Arrays 101

[Rob Sargent]

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  
> 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  
>> 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 
>>>  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  to continue...
>>> 
>>> 
 On Mon, Oct 26, 2015 at 5:49 PM, Gavin Flower 
  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: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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


On Mon, Oct 26, 2015 at 5:49 PM, Gavin Flower
> 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 



--
Adrian Klaver
adrian.kla...@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

Re: [GENERAL] Recursive Arrays 101

[David G. Johnston]

On Mon, Oct 26, 2015 at 10:14 PM, Adrian Klaver 
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: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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  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 
> 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 
> 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.blomst...@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  to continue...
>>>
>>> On Mon, Oct 26, 2015 at 5:49 PM, Gavin Flower <
>>> gavinflo...@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: [GENERAL] Recursive Arrays 101

[Thomas Kellerer]

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

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

Any ideas? 





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

Re: [GENERAL] Recursive Arrays 101

[Rob Sargent]

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: [GENERAL] Recursive Arrays 101

[Jim Nasby]

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


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

Re: [GENERAL] Recursive Arrays 101

[Harald Fuchs]

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



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

Re: [GENERAL] Recursive Arrays 101

[Rob Sargent]

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.





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

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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

Re: [GENERAL] Recursive Arrays 101

[Rob Sargent]

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









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

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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 = ''
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_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.kla...@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

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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 
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.kla...@aklaver.com
>



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

Re: [GENERAL] Recursive Arrays 101

[John R Pierce]

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



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

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

@ 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  wrote:

>
> > On 25 Oct 2015, at 19:38, Adrian Klaver 
> 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: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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 
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.blomst...@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.blomst...@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.kla...@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.kla...@aklaver.com

>>>
>>>
>>>
>>> --
>>> David Blomstrom
>>> Writer & Web Designer (Mac, M$ & Linux)
>>> 

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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 
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.blomst...@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 > > 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.kla...@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: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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 
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 
> 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.kla...@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: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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 
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.kla...@aklaver.com
>



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

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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 
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.kla...@aklaver.com
>



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

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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

Re: [GENERAL] Recursive Arrays 101

[Alban Hertroys]

> On 25 Oct 2015, at 19:38, Adrian Klaver  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.



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

[GENERAL] Recursive Arrays 101

[David Blomstrom]

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: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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