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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
