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

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.

Creating an example table

As an example, the syntax to create Book Town's books table is demonstrated in Example 4-6.

booktown=# CREATE TABLE books (
booktown(#              id integer UNIQUE,
booktown(#              title text NOT NULL,
booktown(#              author_id integer,
booktown(#              subject_id integer,
booktown(#              CONSTRAINT books_pkey PRIMARY KEY (id)
booktown(#              );
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index
'books_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_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. This format is the convention used throughout this 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 list below 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, re-naming the table, re-naming its columns, and adding new columns. As of PostgreSQL 7.1.x, dropping columns from a table is not supported. It is therefore a 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. Example 4-8 shows the syntax of the ALTER TABLE command's ADD COLUMN.

  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. Such a procedure is demonstrated in Example 4-9.

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_pkey

Example 4-9 successfully adds a new column to Book Town's books table with the name of the 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 Re-structuring Existing Tables for information on how to re-structure 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-10 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_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_pkey

Re-naming a table

A table may be safely re-named by passing the RENAME clause to the ALTER TABLE command. The following is the syntax to re-name a table.

  ALTER TABLE table
        RENAME TO new_table

A table may be arbitrarily re-named 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 which an external application relies on.

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

Re-naming columns

A table's columns may be safely re-named in PostgreSQL without modifying the data contained in the table. Re-naming 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 re-name 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 requesting a column re-name, and not a table re-name. This is demonstrated in Example 4-12.

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 constraints may be added to an existing table column. 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 are the only supported constraint 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) is demonstrated in Example 4-13.

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

Any book_id value will now also have to exist in the books table. See the section called Using Constraints in Chapter 7 for more detailed information on 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 (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-14 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

In order to change the ownership of a table, you must either be the owner of that table, or a PostgreSQL superuser.

Re-structuring with CREATE TABLE AS

One common technique of re-structuring a table is to use the CREATE TABLE command in conjunction with the AS clause and a valid SQL query. This allows you to re-structure your existing table into a temporary table, which can then be re-named. 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 which 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 are able to 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 constraint which may be added to the table after is has been created is the foreign key constraint. Once the new table has been created, the old one can be re-named (or destroyed), and the new one can be re-named 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-15.

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_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

Re-structuring 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. This is achieved 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 on using the INSERT INTO command with a SELECT statement, and the section called Retrieving Rows with SELECT for more information on valid SELECT statements.