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