Using Tables

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 [, ... ] ) ]

Creating an example table

Example 4-6 demonstrates the syntax to create Book Town's 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.

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:

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.

Adding columns

You can add a new column to a table using the ALTER TABLE command's ADD COLUMN clause. Here is the syntax for the ALTER TABLE command's ADD COLUMN clause:

  ALTER TABLE table
        ADD [ COLUMN ] column_name column_type

Technically, the COLUMN keyword may be omitted; it is considered a noise term and is only useful for your own readability.

As an example of adding a column, imagine that an industrious employee at Book Town decides that the books table requires another column, specifically, a date column to represent the publication date. Example 4-8 demonstrates such a procedure.

booktown=# ALTER TABLE books
booktown-#       ADD publication date;
ALTER
booktown=# \d books
          Table "books"
  Attribute  |  Type   | Modifier
-------------+---------+----------
 id          | integer | not null
 title       | text    | not null
 author_id   | integer |
 subject_id  | integer |
 publication | date    |
Index: books_id_pkey

Example 4-8 successfully adds a new column to Book Town's books table with the name of publication, and a data type of date. It also demonstrates a pitfall of uncoordinated table design among developers: in our examples, the Book Town editions table already stores the publication date, so the column should not have been added to the books table. See the Section called Restructuring Existing Tables" for information on how to restructure a table after such a mistake has been made.

Setting and removing default values

The most flexible table modification pertains to the default values of columns. These values may be both set and removed from a column with relative ease via the ALTER TABLE command's ALTER COLUMN clause.

The following syntax passed to PostgreSQL describes how to use ALTER TABLE in order to either set, or remove a default value of value from a column named column_name :

  ALTER TABLE table
        ALTER [ COLUMN ] column_name
        { SET DEFAULT value | DROP DEFAULT }

Again, the COLUMN keyword is considered noise, and is an optional term used only for improved readability of the statement. Example 4-9 demonstrates setting and dropping a simple default sequence value on the books table's id column.

booktown=# ALTER TABLE books
booktown-#       ALTER COLUMN id
booktown-#       SET DEFAULT nextval('book_ids');
ALTER
booktown=# \d books
                           Table "books"
 Attribute  |  Type   |                  Modifier
------------+---------+--------------------------------------------
 id         | integer | not null default nextval('book_ids'::text)
 title      | text    | not null
 author_id  | integer |
 subject_id | integer |
Index: books_id_pkey

booktown=# ALTER TABLE books
booktown-#       ALTER id
booktown-#       DROP DEFAULT;
ALTER
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

Renaming a table

A table may be safely renamed by passing the RENAME clause with the ALTER TABLE command. The following is the syntax to rename a table:

  ALTER TABLE table
        RENAME TO new_table

A table may be arbitrarily renamed as many times as you like without affecting the data. This could, of course, be a dangerous thing to do if you are dealing with a table on which an external application relies.

booktown=# ALTER TABLE books RENAME TO literature;
ALTER
booktown=# ALTER TABLE literature RENAME TO books;
ALTER

Renaming columns

A table's columns may be safely renamed in PostgreSQL without modifying the data contained in the table. Renaming a column is a dangerous thing to do because existing applications may use explicit references to column names. If an existing program references a column by name and the column is renamed, the program could cease functioning correctly.

The following syntax describes how to rename a column:

  ALTER TABLE table
        RENAME [ COLUMN ] column_name TO new_column_name;

As with the other ALTER TABLE commands, the COLUMN keyword is considered noise, and may be optionally omitted. The existence of two identifiers separated by the TO keyword provides enough information for PostgreSQL to determine that you are renaming a column, and not a table, as demonstrated in Example 4-11.

booktown=# \d daily_inventory
    Table "daily_inventory"
 Attribute |  Type   | Modifier
-----------+---------+----------
 isbn      | text    |
 in_stock  | boolean |

booktown=# ALTER TABLE daily_inventory
booktown-#       RENAME COLUMN in_stock TO is_in_stock;
ALTER
booktown=# ALTER TABLE daily_inventory
booktown-#       RENAME is_in_stock TO is_stocked;
ALTER

Adding constraints

Constraints may be added in a limited fashion after a table has been created. As of PostgreSQL 7.1.x, only foreign key and check constraints may be added to an existing table column with ALTER TABLE. The following is the syntax to add a constraint to a table:

  ALTER TABLE table
        ADD CONSTRAINT constraint_name constraint_definition

The syntax of the constraint_definition is dependent on the type of constraint you wish to add. As foreign keys and checks are the only supported constraints with the ADD CONSTRAINT clause (as of PostgreSQL 7.1.x), the syntax for adding a foreign key to the editions table (which references the books table's id column) and a check condition on the type column is demonstrated in Example 4-12.

booktown=# ALTER TABLE editions
booktown-#       ADD CONSTRAINT foreign_book
booktown-#       FOREIGN KEY (book_id) REFERENCES books (id);
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s)
for FOREIGN KEY check(s)
CREATE
booktown=# ALTER TABLE editions
booktown-#       ADD CONSTRAINT hard_or_paper_back
booktown-#       CHECK (type = 'p' OR type = 'h');
ALTER

Due to the foreign key constraint, any book_id value in the editions table will now also have to exist in the books table. Additionally, due to the check constraint, the type values within the editions table may only be set to either p or h.

See the Section called Using Constraints in Chapter 7" in Chapter 7 for more detailed information about constraints, their purpose, and their syntax.

Changing ownership

By default, the creator of a table is automatically its owner. The owner has all rights that can be associated with a table, in addition to the ability to grant and revoke rights with the GRANT and REVOKE commands (for more information see Chapter 10). If ownership must be changed, you can use the ALTER TABLE command's OWNER clause. The syntax to change the ownership of a table from one user to another is:

  ALTER TABLE table
        OWNER TO new_owner

Example 4-13 demonstrates altering a table's ownership with the ALTER TABLE command's OWNER clause. In it, corwin is set as the owner of the employees table.

booktown=# ALTER TABLE employees
booktown-#       OWNER TO corwin;
ALTER

Restructuring with CREATE TABLE AS

One common technique of restructuring a table is to use the CREATE TABLE command in conjunction with the AS clause and a valid SQL query. This allows you to restructure your existing table into a temporary table, which can then be renamed. Doing this also allows you to both remove and re-arrange columns to a table by physically re-creating it, and simultaneously re-populating it with data from the original table.

The following syntax describes this limited version of CREATE TABLE, where query is the valid SELECT statement that selects the data to populate the new table with. The data type of each created column is implied by the type of each corresponding column selected by query:

  CREATE [ TEMPORARY | TEMP ] TABLE table
         [ ( column_name [, ...] ) ]
         AS query

The advantage to this technique is that you may create the new table and populate it in a single SQL command. The most notable limitation of this technique is that there is no comprehensive way to set constraints on the newly created table; the only constraints that may be added to the table after is has been created are the foreign key and check constraints. Once the new table has been created, the old one can be renamed (or destroyed), and the new one can be renamed to the name of the original table.

Suppose, for example, that you wanted to modify the books table in order to drop the superfluous publication column which was created in the Section called Adding columns." You can create a limited copy of the table (designating only the desired columns) by passing a valid SELECT statement to the AS clause of CREATE TABLE, and dropping the old table with DROP TABLE, as shown in Example 4-14.

booktown=# \d books
          Table "books"
  Attribute  |  Type   | Modifier
-------------+---------+----------
 id          | integer | not null
 title       | text    | not null
 author_id   | integer |
 subject_id  | integer |
 publication | date    |
Index: books_id_pkey

booktown=# CREATE TABLE new_books
booktown-#        (id, title, author_id, subject_id)
booktown-#        AS SELECT id, title, author_id, subject_id
booktown-#                  FROM books;
SELECT
booktown=# ALTER TABLE books RENAME TO old_books;
ALTER
booktown=# ALTER TABLE new_books RENAME TO books;
ALTER
booktown=# \d books
        Table "books"
 Attribute  |  Type   | Modifier
------------+---------+----------
 id         | integer |
 title      | text    |
 author_id  | integer |
 subject_id | integer |

booktown=# DROP TABLE books;
DROP

Restructuring with CREATE TABLE and INSERT INTO

If you require a more specifically defined table than that created by CREATE TABLE AS (e.g., one with column constraints), you can replicate the effect of the CREATE TABLE AS technique by issuing two SQL statements rather than one. You can achieve this by first creating the new table as you ordinarily would with CREATE TABLE, and then populating the table with data via the INSERT INTO command and a valid SELECT statement.

booktown=# CREATE TABLE new_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)
booktown(# );
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'books_id_pkey'
for table 'new_books'
CREATE
booktown=# INSERT INTO new_books
booktown-#             SELECT id, title, author_id, subject_id
booktown-#                    FROM books;
INSERT 0 12
booktown=# ALTER TABLE books RENAME TO old_books;
ALTER
booktown=# ALTER TABLE new_books RENAME TO books;
ALTER
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

See the Section called Inserting Values from Other Tables with SELECT" for more information about using the INSERT INTO command with a SELECT statement, and the Section called Retrieving Rows with SELECT" for more information about valid SELECT statements.