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:
(rules on what data is or is not allowed within a column), and
(general limitations and relationships defined on the table itself).
The following is the syntax for CREATE TABLE with a detailed
explanation of the terms used:
CREATE [ TEMPORARY | TEMP ] TABLE
[ ... ] ] |
[, ... ]
) [ INHERITS (
[, ... ] ) ]
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.
identifies your table's name (once created).
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
, followed by a valid data
, and may optionally include a
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
[, ... ]
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.
[, ...] )
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
. 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.
Example 4-6 demonstrates the syntax to create Book Town's
Example 4-6. Creating the books table
CREATE TABLE books (
id integer UNIQUE,
title text NOT NULL,
CONSTRAINT books_id_pkey PRIMARY KEY (id));
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'books_id_pkey'
for table 'books'
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
Once created, you may use the \d describe command (followed by the table name)
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
, 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
Attribute | Type | Modifier
id | integer | not null
title | text | not null
author_id | integer |
subject_id | integer |
The following list provides a more detailed explanation of the fields and terms shown in Example 4-7:
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
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
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.
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.
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.
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).
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.