Re: Recursive Arrays 101 - Mailing list pgsql-general

Hi David,

Please don't top post!

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

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

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

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

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

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

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

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



Cheers,
Gavin