ALTER TABLE in PostgreSQL Database

PostgreSQL supports the ALTER TABLE statement to modify the structure of the existing tables.

ALTER table can be used to

Add column
Rename column
Modify column datatype
Set or Remove default value to column
Add or Drop constraint to column
Drop column in the table

The general syntax of the ALTER TABLE statements:

Syntax:

ALTER TABLE action

Where action can be:
ADD [ COLUMN ] 
DROP COLUMN
ALTER COLUMN TYPE
ALTER COLUMN SET DEFAULT
ALTER COLUMN DROP DEFAULT 
ALTER COLUMN SET attribute_option
ALTER COLUMN RESET 
ADD table_constraint
VALIDATE CONSTRAINT
DISABLE TRIGGER
ENABLE TRIGGER
ENABLE REPLICA TRIGGER trigger_name
ENABLE ALWAYS TRIGGER trigger_name
DISABLE RULE rewrite_rule_name
ENABLE RULE rewrite_rule_name
ENABLE REPLICA RULE rewrite_rule_name
ENABLE ALWAYS RULE rewrite_rule_name
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITH OIDS
SET WITHOUT OIDS
SET ( storage_parameter = value [, ... ] )
RESET ( storage_parameter [, ... ] )
INHERIT parent_table
NO INHERIT parent_table
OF type_name
NOT OF
OWNER TO new_owner
SET TABLESPACE new_tablespace

Consider the following employee table:

Let's see the overview of ALTER TABLE statements:

To add a column of type varchar to a table:

Example: Add Column

ALTER TABLE employee ADD COLUMN address varchar(50);

To drop a column from a table:

Example: Drop Column

ALTER TABLE employee DROP COLUMN employee CASCADE;

To change the types of two existing columns in one operation:

Example: Alter Column Type

ALTER TABLE employee
    ALTER COLUMN first_name TYPE varchar(80),
    ALTER COLUMN last_name TYPE varchar(80);

To rename an existing column:

Example: Rename Column

ALTER TABLE employee RENAME COLUMN birthdate TO BoD;

To rename an existing table:

Example: Rename Table

ALTER TABLE employee RENAME TO employee_info;

To add a NOT NULL constraint to a column:

Example: Add NOT NULL Constraint

ALTER TABLE employee ALTER COLUMN gender SET NOT NULL;

To remove a NOT NULL constraint from a column:

Example: Remove NOT NULL Constraint

ALTER TABLE employee ALTER COLUMN salary DROP NOT NULL;

To add a check constraint to a table and all its children:

Example: Add Check Constraint

ALTER TABLE employee ADD CONSTRAINT first_name CHECK (char_length(first_name) > 1);

To remove a check constraint from a table and all its children:

Example: Remove Check Constraint

ALTER TABLE employee DROP CONSTRAINT first_name;
ALTER TABLE ONLY employee DROP CONSTRAINT first_name; -- remove check constraint from one table only

To add a primary key in a table:

Example: Add Primary Key

ALTER TABLE employee ADD PRIMARY KEY (id);

To move a table to a different tablespace:

Example: Move Table to Tablespace

ALTER TABLE employee SET TABLESPACE emptablespace;

To move a table to a different schema:

Example: Move Table to Schema

ALTER TABLE myschema.employee SET SCHEMA yourschema;

Learn about all the ALTER TABLE statements in next chapters.