When you create a table and you realize that you made a
mistake, or the requirements of the application change, you can
drop the table and create it again. But this is not a convenient
option if the table is already filled with data, or if the table
is referenced by other database objects (for instance a foreign
key constraint). Therefore PostgreSQL provides a family of commands to
make modifications to existing tables. Note that this is
conceptually distinct from altering the data contained in the
table: here we are interested in altering the definition, or
structure, of the table.

You can:

Add columns

Remove columns

Add constraints

Remove constraints

Change default values

Change column data types

Rename columns

Rename tables

All these actions are performed using the ALTER TABLE command, whose reference
page contains details beyond those given here.

In fact all the options that can be applied to a column
description in CREATE TABLE can be
used here. Keep in mind however that the default value must
satisfy the given constraints, or the ADD will fail. Alternatively, you can add
constraints later (see below) after you've filled in the new
column correctly.

Tip: Adding a column with a default requires
updating each row of the table (to store the new column
value). However, if no default is specified, PostgreSQL is able to avoid the
physical update. So if you intend to fill the column with
mostly nondefault values, it's best to add the column with
no default, insert the correct values using UPDATE, and then add any desired default as
described below.

Whatever data was in the column disappears. Table
constraints involving the column are dropped, too. However, if
the column is referenced by a foreign key constraint of another
table, PostgreSQL will not
silently drop that constraint. You can authorize dropping
everything that depends on the column by adding CASCADE:

ALTER TABLE products DROP COLUMN description CASCADE;

See Section 5.12 for a
description of the general mechanism behind this.

To remove a constraint you need to know its name. If you
gave it a name then that's easy. Otherwise the system assigned
a generated name, which you need to find out. The psql command \d
tablename can be helpful
here; other interfaces might also provide a way to inspect
table details. Then the command is:

ALTER TABLE products DROP CONSTRAINT some_name;

(If you are dealing with a generated constraint name like
$2, don't forget that you'll need to
double-quote it to make it a valid identifier.)

As with dropping a column, you need to add CASCADE if you want to drop a constraint that
something else depends on. An example is that a foreign key
constraint depends on a unique or primary key constraint on the
referenced column(s).

This works the same for all constraint types except not-null
constraints. To drop a not null constraint use:

This will succeed only if each existing entry in the column
can be converted to the new type by an implicit cast. If a more
complex conversion is needed, you can add a USING clause that specifies how to compute the
new values from the old.

PostgreSQL will attempt to
convert the column's default value (if any) to the new type, as
well as any constraints that involve the column. But these
conversions might fail, or might produce surprising results.
It's often best to drop any constraints on the column before
altering its type, and then add back suitably modified
constraints afterwards.