This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
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
 
Continue to: