ALTER TABLE

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.

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