Inputs

Outputs

ALTER

Message returned from column or table renaming.

NEW

Message returned from column addition.

ERROR

Message returned if table or column is not
available.

Description

ALTER TABLE changes the definition of
an existing table. The new columns and their types are specified
in the same style and with the the same restrictions as in
CREATE TABLE. The RENAME clause causes the
name of a table or column to change without changing any of the
data contained in the affected table. Thus, the table or column
will remain of the same type and size after this command is
executed.

You must own the table in order to change its schema.

Notes

The keyword COLUMN is noise and can
be omitted.

“[*]” following a name of a table indicates that statement
should be run over that table and all tables below it in the
inheritance hierarchy. The PostgreSQL
User's Guide has further information on inheritance.

Refer to CREATE TABLE for a further
description of valid arguments.

Usage

To add a column of type VARCHAR to a table:

ALTER TABLE distributors ADD COLUMN address VARCHAR(30);

To rename an existing column:

ALTER TABLE distributors RENAME COLUMN address TO city;

To rename an existing table:

ALTER TABLE distributors RENAME TO suppliers;

Compatibility

SQL92

ALTER TABLE/RENAME is a Postgres language extension.

SQL92 specifies some additional capabilities for ALTER TABLE statement which are not yet directly
supported by Postgres:

Puts the default value or constraint specified into the
definition of column in the table. See CREATE TABLE for the syntax of the default
and table-constraint clauses. If a default clause already
exists, it will be replaced by the new definition. If any
constraints on this column already exist, they will be
retained using a boolean AND with the new constraint.

Currently, to set new default constraints on an existing
column the table must be recreated and reloaded:

Removes the default value specified by default or the
rule specified by constraint from the definition of a
table. If RESTRICT is specified only a constraint with no
dependent constraints can be destroyed. If CASCADE is
specified, Any constraints that are dependent on this
constraint are also dropped.

Currently, to remove a default value or constraints on
an existing column the table must be recreated and
reloaded:

Removes a column from a table. If RESTRICT is specified
only a column with no dependent objects can be destroyed.
If CASCADE is specified, all objects that are dependent on
this column are also dropped.

Currently, to remove an existing column the table must
be recreated and reloaded: