Modifies table and column attributes.
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
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).
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 the reference entry titled “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 the reference
entry titled “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 the reference entry titled “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.
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
3.135.198.174