ALTER TABLE

Modify the attributes of a table.

ALTER TABLE [ IF EXISTS ] name 
ADD column_name data_type [ column_constraint [ ... ] ] |
ADD table_constraint 
ALTER [ COLUMN ] column_name SET DEFAULT expression |
ALTER [ COLUMN ] column_name DROP DEFAULT |
ALTER [ COLUMN ] column_name DROP NOT NULL |
ALTER [ COLUMN ] varchar_column_name VARCHAR(new_length) |
DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]
OWNER TO { new_owner | CURRENT_USER | SESSION_USER } |
RENAME column_name TO new_column_name |
RENAME TO new_name |
SET SCHEMA new_schema
IF EXISTS
Alter the table if it exists, but do not return an error message if it does not exist.
ADD column_name
Add a column to a table. You must specify the data type. You can also specify column constraints, such as UNIQUE, NULL, or DEFAULT; however, you cannot add a column with a NOT NULL constraint.
Note: When you add a column with a DEFAULT constraint, the default value is applied only for new rows that are inserted after the column was added. Existing rows in the table will contain a NULL value for that column.

See CREATE TABLE for complete column constraint syntax.

You cannot drop columns from tables. ALTER TABLE DROP COLUMN is not supported.

ADD table_constraint
Add a table constraint: PRIMARY KEY or UNIQUE (see CREATE TABLE for the complete syntax).
Note: PRIMARY KEY and UNIQUE constraints are not enforced.
SET DEFAULT expression
Define a default value for the column, which applies only to new rows that are inserted or updated; existing rows do not change.
DROP DEFAULT
Drop the default value for the column. Make sure the expression is compatible with the data type of the column. This change applies only to new rows that are inserted or updated; existing rows do not change.
DROP NOT NULL
Drop the NOT NULL constraint for the column. NULL values will be allowed in new rows. This command applies only to columns that were defined NOT NULL. (SET NOT NULL is not supported.)
varchar_column_name
Specify an existing VARCHAR column in the table. The column must be a VARCHAR column; you cannot alter column lengths for other data types. You cannot alter the length of a column that is referenced in a view.
VARCHAR(new_length)
The new length of the VARCHAR column you want to modify. This number must be greater than the current length of the column. For example, you can change a VARCHAR(20) column to a VARCHAR(50).
DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]
Drop a constraint on a table, such as a foreign key constraint. The CASCADE option automatically drops objects that depend on the dropped constraint. The RESTRICT option (the default) does not drop the constraint if dependent objects exist.
OWNER TO
Change the owner of the table.
RENAME TO
Rename a column in the table or the name of the table itself.
SET SCHEMA
Change the schema that the table belongs to.

Examples

Change the schema of a table:
premdb=# alter table team set schema private;
ALTER TABLE
Add a new column to a table:
premdb=# alter table matchstats add column matchid uuid null;
ALTER TABLE
Define a default value for a column:
premdb=# alter table match alter column matchday set default '2017-10-21';
ALTER TABLE
Remove a default value for a column:
premdb=# alter table match alter column matchday drop default;
ALTER TABLE
Remove a NOT NULL constraint from a column:
premdb=# alter table newmatchstats alter column seasonid drop not null;
ALTER TABLE
Add a UNIQUE constraint to a table:
premdb=# alter table match add constraint uniquematch unique(matchid);
ALTER TABLE
Increase the length of the VARCHAR column city in the team table. The column was originally defined as VARCHAR(20).
premdb=# alter table team alter column city varchar(30);
ALTER TABLE
Drop a foreign-key constraint from a table:
premdb=# alter table match drop constraint match_seasonid_fkey;
ALTER TABLE