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