Practical PostgreSQL

Alter Table

Modifies table and column attributes.

Synopsis

ALTER TABLE table [ * ]
      ADD [ COLUMN ] column type
ALTER TABLE table [ * ]
      ALTER [ COLUMN ] column { SET DEFAULT defaultvalue | DROP DEFAULT }
ALTER TABLE table [ * ]
      RENAME [ COLUMN ] column TO newcolumn
ALTER TABLE table
      RENAME TO newtable
ALTER TABLE table
      ADD CONSTRAINT newconstraint definition
ALTER TABLE table
      OWNER TO newowner

Parameters

table

The name of the (existing) table you intend to modify.

column

The name of a new column, or existing column that you intend to modify.

type

The data type of a new column being created. (This is used only during the creation of a new column.)

defaultvalue

A new default value for the specified column.

newcolumn

A new name for column.

newtable

A new name for table.

newconstraint definition

The name and definition of a new table constraint to be added to an existing table. See Chapter 7, for more details on how to define a table constraint.

newowner

The new owner of table (when transferring ownership).

Results

ALTER

The message returned when a column or table modification is completed successfully.

ERROR

The message returned if the table or column modifications cannot be completed, along with an explanation of what failed, if possible.

Description

The ALTER TABLE command is used to modify the structure of tables existing within a database in various ways. To rename a column or table, use the RENAME keyword. Renaming in this manner will not alter any of the data in either a column or a table. If you wish to ...