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


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
    RENAME TO newtable
    ADD table constraint definition



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


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


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.)


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


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.



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


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


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.


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


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