Databases - Practical PostgreSQL - Creating Tables with CREATE TABLE

Databases - Practical PostgreSQL
Previous Page	Home	Next Page

Creating Tables with CREATE TABLE

The SQL command to create a table is CREATE TABLE. This command requires, at a minimum, the name for the new table and a description for each column, which consists of the column name and data type. The CREATE TABLE command accepts several optional parameters: column constraints (rules on what data is or is not allowed within a column), and table constraints (general limitations and relationships defined on the table itself).

CREATE TABLE syntax

The following is the syntax for CREATE TABLE with a detailed explanation of the terms used:

  CREATE [ TEMPORARY | TEMP ] TABLE 
table_name
 (
         { 
column_name
 
type
 [ 
column_constraint
 [ ... ] ] | 
table_constraint
 }
         [, ... ]
         ) [ INHERITS ( 
inherited_table
 [, ... ] ) ]

TEMPORARY | TEMP: The TEMPORARY or TEMP SQL keyword causes the created table to be automatically destroyed at the end of the active session to PostgreSQL. A temporary table may have the same name as an existing table, and until the temporary table is destroyed, any references to that table name will utilize the temporary table. Any indices placed on this table are temporary and will be destroyed in the same fashion at the end of the session.
table_name: table_name identifies your table's name (once created).
column_name type [ column_constraint ] | table_constraint: Each table column and table constraint is defined within the parentheses following the table name, separated by commas. Column definitions must contain a valid identifier for a column_name, followed by a valid data type, and may optionally include a column_constraint. The requirements of column constraint definitions are dependent on the constraints, described in the Section called Using Constraints in Chapter 7" in Chapter 7. Table constraints and columns may be mixed in this grouped list, though it is common practice to list columns first, followed by any table constraints.
[, ... ]: Each column definition may be followed by a comma in order to define a subsequent column after it. The ellipses denote that you may enter as many columns as you wish (up to the limit of 1,600). Be sure that you do not follow the last column or constraint in the list with a comma, as is allowed in languages like Perl; this will cause a parsing error.
INHERITS ( inherited_table [, ...] ): The object-relational capabilities of PostgreSQL allow you to specify one or more tables (in a grouped, comma-delimited list) from which your table will inherit . This optional specification creates an implied parent-child relationship between tables. This relatively new technique to RDBMSs is discussed in more detail in the Section called Inheritance in Chapter 7" within Chapter 7.

Note: The terms column_constraint and table_constraint in the above syntax definition refer to sets of potentially complex constraint definitions. The syntax for these various constraints is listed in detail in the Section called Using Constraints in Chapter 7" within Chapter 7.

Creating an example table

Example 4-6 demonstrates the syntax to create Book Town's books table.

Example 4-6. Creating the books table

booktown=# 
CREATE TABLE books (

booktown(# 
             id integer UNIQUE,

booktown(# 
             title text NOT NULL,

booktown(# 
             author_id integer,

booktown(# 
             subject_id integer,

booktown(# 
             CONSTRAINT books_id_pkey PRIMARY KEY (id));

NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'books_id_pkey' 
for table 'books'
CREATE

The CREATE output following the execution of the statement indicates that the table was successfully created. If you receive an error message, check your punctuation and spelling to make sure you have entered the correct syntax. Receiving no message at all means that you probably left open a quote, parenthesis, or other special character symbol.

Additionally, the NOTICE statement serves to inform you that in order to properly complete the creation of this table as described, an implicit index called books_ id_ pkey will be created.

Examining a created table

Once created, you may use the \d describe command (followed by the table name) within psql to display the structure of the table and its constraints (if any). Example 4-7 shows the output of \d when it is used to describe the books table created in the last section.

Notice that this format does not show actual row data, but instead places each column and its attributes in its own row , essentially turning the table on its side. This is done for the sake of clarity, as many tables can grow too large to fit on a screen (or on a page) horizontally. We'll use this format throughout the book when examining table structure without data.

Example 4-7. The \d command's output

booktown=# 
 \d books

          Table "books"
 Attribute  |  Type   | Modifier
------------+---------+----------
 id         | integer | not null
 title      | text    | not null
 author_id  | integer |
 subject_id | integer |
Index: books_id_pkey

The following list provides a more detailed explanation of the fields and terms shown in Example 4-7:

id

The id column is a numeric identifier unique to each book. It is defined as being of the data type integer, and has on it the following constraints:

UNIQUE: This constraint ensures that the column always has a unique value. A column with the UNIQUE constraint set may ordinarily contain empty (NULL values, but any attempt to insert duplicate values will fail. The id column is also designed to be used as the PRIMARY KEY.
PRIMARY KEY: While not displayed in the \d breakdown, you can see in our original CREATE TABLE statement that this table's primary key is defined on the id column. Placing the constraint of PRIMARY KEY on a column implicitly sets both the NOT NULL and UNIQUE constraints as well.
NOT NULL: This constraint is set automatically by setting the PRIMARY KEY constraint. It ensures that the ID column always has a value. Data for this column can never be empty, and any attempt to insert NULL values will fail.

title

The title column of the table must contain character strings of type text. The text type is more flexible than varchar, and is a good choice for this column as it does not require that you specify the maximum number of characters allowed. This column has the NOT NULL constraint set, indicating that a row's title column cannot ever be set to NULL.

author_id

The author_id column must contain values of type integer, and relates to the authors table. There are no constraints placed on this column, as sometimes an author may not be known for a title (making NOT NULL inappropriate), and an author may show up more than once (making UNIQUE inappropriate as well).

subject_id

The subject_id is similar to the author_id column, as it may contain values of type integer, and relates to the subjects table. Again, there are no constraints on the contents of this column, as many books may be uncategorized, or fall under the same subject.

While a table's structure can be modified after it has been created, the available modifications are limited. These include, for example, renaming the table, renaming its columns, and adding new columns. PostgreSQL 7.1.x does not support dropping columns from a table. It is therefore good practice to thoughtfully and carefully plan your table structures before creating them.

Databases - Practical PostgreSQL
Previous Page	Home	Next Page