Practical PostgreSQL
PrevNext

ALTER TABLE

Name

ALTER TABLE  --  Modifies table, row, and column attributes.

Synopsis

ALTER TABLE table [ * ]
    ADD [ COLUMN ] column type
ALTER TABLE table [ * ]
    ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT }
ALTER TABLE table [ * ]
    RENAME [ COLUMN ] column TO newcolumn
ALTER TABLE table
    RENAME TO newtable
ALTER TABLE table
    ADD table constraint definition

Parameters

table

This parameter takes the name of the (existing) table you wish to modify.

column

Use this parameter to provide the name of a new or existing column that you wish to modify.

type

Use this parameter to choose the data type of the column to be created. (This is used only during the dreation of a new column.)

newcolumn

To rename an existing column, enter its new name as this parameter.

newtable

If renaming an existing table, enter its new name as this parameter.

table constraint definition

If you wish to add new constraints to an existing table, enter them as this parameter.

new user

To change ownership of a table, enter the username of the new owner as this parameter.

Results

ALTER

This is the message displayed if the column or table modifications are completed successfully.

ERROR

This message is displayed if the table or column modifications cannot be completed.

Description

The ALTER TABLE command is used to modify the definition of tables existing within a database in various ways. To rename a column or table, use the RENAME clause. 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 constraint to a table, use the ADD table constraint definition clause with the same syntax as CREATE TABLE.

To add a new column to a table, use ADD COLUMN with the same syntax as CREATE TABLE. To modify or delete a column's default setting, use ALTER COLUMN. (Remember that defaults are only applicable to newly added rows, and will not affect existing rows).

As of the most current version of PostgreSQL available at this printing (7.1.x), you are not able to set the default or constraint settings for a new column. You can, however, use the SET DEFAULT clause of ALTER TABLE to set the defaults after the column is created. If you do this, be sure to use UPDATE to update the data in the rest of the columns to the new default. Also note that the only constraint that can be added to a table with ALTER TABLE currently is the FOREIGN KEY constraint. To add a unique constraint, create a unique index uising CREATE INDEX. To add check contraints, you will have to recreate and reload the table in question.

NoteOwnership
 

You will need to be the owner of a table before you'll be able to modify it.

Examples

The following example adds a TEXT column named address to the employees table. 

ALTER TABLE employees ADD COLUMN address TEXT;

Now, the previously added address column is renamed to mailing_addr. 

ALTER TABLE employees RENAME COLUMN address TO mailing_addr;

The following example renames the table employee to personnel 

ALTER TABLE employee RENAME TO personnel;

The following example changes the owner of the personnel table to the user account jonathan. 

ALTER TABLE personnel OWNER TO jonathan;

The following example to add a foreign key constraint to the personnel table. 

ALTER TABLE personnel ADD CONSTRAINT pers_fkey FOREIGN KEY (mailing_addr) REFERENCES addresses(mailing_addr) MATCH FULL

PrevHomeNext
ALTER GROUPUpALTER USER