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 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 in the table called table_name :

  ALTER TABLE table_name
        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-8 demonstrates setting and dropping a simple default sequence value on the books table's id column.

Example 4-8. Altering Column Defaults

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