Creating Tables and Columns

Not Completed

Database Design

Before starting a new database, you would really want to create a database design and a database dictionary. These are required documentation for any database that is created for an organization or for distribution. For now, we are just going to "play" in the database and we'll come back to design a little later.

Creating Tables

It's relatively easy to create tables in PostgreSQL but you do need to follow some steps exactly to be successful.

• All table names and column names need to be in lower case.
• All tables should have a "primary key" which is a "serial" data type and is called "id".

If you create the "primary key" mentioned above, then each time you add a record to the table, PostgreSQL will automatically add an entry in the "id" field that contains a sequential integer value. In other words, the records will be numbered 1,2,3,4,... This is very important to make sure that you have unique records in the database and it also makes queries on the "id" field very fast regardless of the size of the database.

The steps below will walk you through creating such a table.

1. Open PostgreSQL and navigate to the database you want to create the table in and then navigate down to the "Tables" entry (Database -> Schemas -> public -> Tables)
2. Right click on "Tables" and select "New Table..."
3. Give the table a descriptive, short, unique name.
4. Change the "Owner" to "postgres"
5. Click on the "Columns" table and click "Add"
6. Give the column the "Name" of "id" and a "Data type" of serial
7. Click on the "Definition" tab and check "Not NULL"
8. Click "OK"
9. Click on the "Constraints" tab and then click on "Add". "Primary Key" should appear next to the "Add" button
10. The "Name" of the primary key should be "<table name>_pkey". This is a convension within PostgreSQL.
11. Cilck on the "Columns" tab.
12. Select "id" in the "Column" popup and then click "Add".
13. Right click on "Tables" and you should see the new table appear.

After adding the table, click on it and you'll see some code in the "SQL Pane" similar to what is below:

-- Table: public.test2
     
-- DROP TABLE public.test2;
     
CREATE TABLE public.test2
(
       id integer NOT NULL DEFAULT nextval('test2_id_seq'::regclass),
       CONSTRAINT test4_pkey PRIMARY KEY (id)
)
WITH (
    OIDS=FALSE
);
ALTER TABLE public.test2
    OWNER TO postgres;

This code contains everything you did in the dialog boxes. Go ahead and go back into the dialog to create a new table and click on "Help". At first this may not appear every helpful. This is the documentation for the SQL command "CREATE TABLE" and includes all the possible parameters. As stated before, we are only using a subset of SQL and we can do almost everything through the GUI but if you want to access all the features of SQL, you'll need to start learning the language.

Adding Columns

1. Navigate to a table you want to add a column to and open the table and then open "Columns".
2. Right click on "Columns" and select "New Column..."
3. Give the column a good name
4. The data type is the single most important setting you'll make when creating columns. I recommend one of the following database for PostgreSQL:

Data Type	Values	When to use
integer	32-bit Integer values from about -2 billion to 2 billion	All integer values All foreign keys Categorical values Counts
character varying(1000)	String values up to the specified length	All strings (names, addresses, descriptions, etc.)
date	SQL Date Formatted dates: YYYY-MM-DD HH:MM:SS	All dates
double precision	64-bit floating point, about 15 digits of precision	All continuous values (DBH, coordinate values, etc.)
geometry	spatial data	Always for polylines and polygons, sometimes for points
character(100)	String values which are the specified length (they will be padded with spaces on the right)	Codes that are fixed-length

The values for the coordinates for points (Lat/Lon or Easting/Northing) can either be stored in a geometry or in two columns. It is far more common to store them in two separate fields. The tradeoff is that you cannot use the special spatial SQL commands for coordinates in separate fields while you can for coordinates in geometries.

For more information see the PostgreSQL documentation on data types and the Geometry Types.