PostgreSQL - ALTER TABLE

The ALTER TABLE statement in PostgreSQL is used to modify the structure of an existing table without deleting the existing data.

• Modifies table structure by adding, modifying, dropping, or renaming columns and tables.
• Adds or removes constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE and CHECK.

Syntax

ALTER TABLE table_name action;

Where:

• table_name: The name of the existing table to be modified.
• action: The operation to perform, such as ADD COLUMN, DROP COLUMN, ALTER COLUMN, RENAME COLUMN, RENAME TO or adding/removing constraints.

Examples

Before using the ALTER TABLE commands, create the following employee table.

Example 1: Add a New Column

Firstly, add an email column to the existing employee table.

Query:

ALTER TABLE employee 
ADD COLUMN email VARCHAR(100);

Output:

Example 2: Rename a Column

The following query renames the department column to department_name.

Query:

ALTER TABLE employee 
RENAME COLUMN Department TO department_name;

Output:

Example 3: Change the Data Type of a Column

The following query increases the maximum length of the first_name column.

Query:

ALTER TABLE employee 
ALTER COLUMN first_name TYPE VARCHAR(100);

Output:

Example 4: Rename the Table

The following query renames the employee table to employees.

Query:

ALTER TABLE employee 
RENAME TO employees;

Example 5: Add a CHECK Constraint

The following query adds a constraint to ensure that the salary is always greater than or equal to 30000.

Query:

ALTER TABLE employee
ADD CONSTRAINT chk_salary
CHECK (salary >= 30000);

Example 6: Drop a Column

The following query removes the email column from the employee table.

Query:

ALTER TABLE employee
DROP COLUMN email;

Output: