PostgreSQL - ALTER TABLE

The PostgreSQL ALTER TABLE statement is used to add, delete or modify columns in an existing table. It is also used to add or drop PostgreSQL constraints on an existing table.

Syntax

The syntax for using ALTER TABLE statement to ADD, DROP and MODIFY columns in PostgreSQL are given below:

/* ADD Column */
ALTER TABLE table_name
ADD column_name datatype;

/* DROP Column */
ALTER TABLE table_name
DROP COLUMN column_name;

/* MODIFY Column */
ALTER TABLE table_name
ALTER COLUMN column_name TYPE datatype;

Example:

Consider a database table called Employee with the following records:

• Add a column: To add a column named Salary in the Employee table, the statement is given below:

  ALTER TABLE Employee
  ADD Salary DECIMAL(18,2);
  

  
  

  This will produce the result as shown below:

  EmpID	Name	City	Age	Salary
  1	John	London	25
  2	Marry	New York	24
  3	Jo	Paris	27
  4	Kim	Amsterdam	30

• Drop a column: To drop the Salary column from the Employee table, the following statement can be used:

  ALTER TABLE Employee
  DROP COLUMN Age;
  

  
  

  This will produce the below mentioned result:

  EmpID	Name	City
  1	John	London
  2	Marry	New York
  3	Jo	Paris
  4	Kim	Amsterdam

• Modify a column: In the original Employee table, the datatype of City column is VARCHAR(100). To change the datatype of the column to VARCHAR(255), the statement is given below:

  ALTER TABLE Employee
  ALTER COLUMN City TYPE VARCHAR(255);
  

  
  

  This will change the datatype of City column from VARCHAR(100) to VARCHAR(255). For a complete reference of all the data types available in PostgreSQL, see Data Types reference.