ALTER TABLE -- Modifies table and column attributes.
Synopsis
ALTER TABLE table [ * ]
ADD [ COLUMN ] columntype
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 newconstraintdefinition
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 add a new table constraint to a table, use the ADD CONSTRAINT clause with the same constraint syntax used with CREATE TABLE for a table constraint (see CREATE TABLE").
As of the most current version of PostgreSQL available at this printing (7.1.x), the only constraints that can be added to a table through the ADD CONSTRAINT clause are the CHECK and FOREIGN KEY constraints. To implicitly add a UNIQUE constraint, a workaround is to create a UNIQUE index, using the CREATE INDEX command (see CREATE INDEX"). To add any other constraints, you will have to recreate and reload data into the table in question.
To add a new column to a table, use ADD COLUMN with the same column syntax used in CREATE TABLE (see CREATE TABLE"). To modify or delete a column's default setting, use ALTER COLUMN with either the SET DEFAULT or DROP DEFAULT clause. (Remember that defaults are only applicable to newly added rows, and will not affect existing rows.)
As of PostgreSQL 7.1.x, you are not able to set the default value or constraint settings for a column at the same time as when it is added with the ADD COLUMN clause. You can, however, use the SET DEFAULT clause of ALTER TABLE to set the default values after the column is created. If you do this after the table has been in use for any period of time, be sure to use the UPDATE command to update the column's data in any existing rows to the new default.
Note: You must be the owner of a table, or a superuser, in order to modify it.
Examples
The following example adds a text column named address to the employees table:
booktown=# ALTER TABLE employees ADD COLUMN address text;
ALTER
Next, the newly added address column is renamed to mailing_address:
booktown=# ALTER TABLE employees RENAME COLUMN address TO mailing_address;
ALTER
The following example renames the employees table to personnel:
booktown=# ALTER TABLE employees RENAME TO personnel;
ALTER
The following example then changes the owner of the personnel table to the PostgreSQL user jonathan:
booktown=# ALTER TABLE personnel OWNER TO jonathan;
ALTER
Finally, the following syntax adds a FOREIGN KEY constraint to the schedules table named valid_employee, which verifies the employee id column in the personnel table:
booktown=# ALTER TABLE schedules ADD CONSTRAINT valid_employee
booktown-# FOREIGN KEY (employee_id)
booktown-# REFERENCES personnel (id) MATCH FULL;
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for
FOREIGN KEY check(s)
CREATE
