ALTER TABLE alters the definition of an existing table. It uses this basic syntax:

Where:

1. name is the name of the table to be altered.
2. new_name is the new name of the table when renaming it.
3. column_name is the name of an existing column in the table.
4. new_column_name is the new name of an existing column when it is renamed.
5. constraint_name is the name of an existing constraint.
6. new_constraint_name is the new name of an existing constraint when it is renamed.

And action is one of:

Where:

1. column_name is the name of an existing column in the table (in the case of ALTER or DROP) or a name that differs from any existing column names (in the case of ADD).
2. column_constraint is a valid column constraint (for more information on valid column constraints, see Constraints in the CREATE TABLE section).
3. data_type is a valid SQL data type.
4. type_expr is a valid type cast conversion expression or function.
5. default_expr is an expression that evaluates to a value with the same type as the column.
6. table_constraint is a valid table constraint (for more information on valid table constraints, see Constraints in the CREATE TABLE section).
7. constraint_name is a valid constraint name.

ADD COLUMN

Appends a new column to the table definition. The syntax following ADD [ COLUMN ] is the same as the syntax for a column definition in CREATE TABLE.

DROP COLUMN

Removes a column from the table definition.

TYPE

Changes the data type of a column.

It is not always possible to alter a data type to another type directly. In particular, it isn’t possible to directly alter a less specific type to a more specific one. For example, text cannot be directly converted to integer, since there are text values that cannot be expressed as integers. So, there is no implicit cast from text to integer.

Which types can be implicitly cast to which other types is defined internally.

When there is no implicit cast, an explicit cast must be specified with the USING clause. For example:

Another use for the USING clause is to truncate any values that are too long for a new type. Suppose we have a column with the type varchar(50). We want to alter that to varchar(25). If there are values longer than 25 characters in the column, then we’ll get an error. We can avoid this by truncating any such values to 25 characters:

SET/DROP DEFAULT

Sets or drops the default value for a column.

SET/DROP NOT NULL

Sets or drops the NOT NULL constraint for a column. Note that SET NULL is incorrect syntax, which can be confusing since a statement like ADD COLUMN col1 int NULL is correct, explicitly setting the column to the default of allowing null values. DROP NOT NULL is the only correct syntax.

ADD table_constraint

Adds a new table constraint to the table.

All of the constraints that can be defined with CREATE TABLE can also be added to existing columns in ALTER TABLE, although the procedure and syntax are a bit different:

1. Column-only constraints (NOT NULL and DEFAULT) have their own syntax, listed above in the action list, and can be added directly.
2. To add any other constraint using the column form, it is necessary to drop the column and add it again with the constraint defined (using ADD [COLUMN], followed by the same column-definition syntax as is used in CREATE TABLE).
3. A table constraint uses ADD, followed by the appropriate syntax for adding it with CREATE TABLE.

DROP CONSTRAINT

Drops an existing constraint from the table (other than NOT NULL or DEFAULT, which use the specific DROP NOT NULL or DROP DEFAULT syntax).

Altering the Constraints in an Existing Table

Existing constraints cannot be altered directly. To alter an existing constraint, ALTER TABLE must be used to drop it and add it again in its altered form.

Dropping a column and adding it in again alters the order of the columns in the table (unless the dropped column is the last one). For example, if there is a table test with an existing column of type serial called id, and it is necessary to make id a primary key, we could do this to use the column form:

Or, we could use the table form:

Dropping the id column to the bottom of the table doesn’t create any problems from SQL’s standpoint, but it can look a bit strange to us humans, who are used to seeing the primary key at the top of the column list. So, in general, it’s best to avoid changing the order of the columns in the table when possible.

Because of this, the table form is preferable when altering a table to add constraints, even when the constraint only references one column. There are no performance concerns with doing it one way or the other: the column form is simply a notational convenience (with, of course, the exception of NOT NULL and DEFAULT). Both the column and the table forms are stored in the same way internally.