From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | David Blomstrom <david(dot)blomstrom(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Recursive Arrays 101 |
Date: | 2015-10-25 18:38:09 |
Message-ID: | 562D2191.4030404@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2015-10-25 21:07:55 | Re: Recursive Arrays 101 |
Previous Message | David Blomstrom | 2015-10-25 18:12:51 | Re: Recursive Arrays 101 |