The PostgreSQL Alter statements are used to make modifications in the structure of the existing table such as adding or dropping a column, modifying the data type, adding the constraints, disabling and enabling the triggers, and so on.
In this section of the PostgreSQL tutorial, we will see the ALTER TABLE command with examples.
1. Alter Table Add New Column
The PostgreSQL Alter table command can be used to add a new column to the existing table.
In this example, we will add a new column named product_catagory in the existing table PRODUCTS_DETAIL.
Syntax:
The syntax of the PostgreSQL Alter table command is as mentioned below.
Command:
Output:
We can see in the below output that the column product_catagory has been added to the table name products_detail.
2. Alter Table Drop Column
In this example, we will use the Alter Table statement to drop an existing column named product_catagory. Once the column is dropped, we will verify the structure of the table using the \d tablename psql command.
Command:
Output:
3. Alter Column to Set not-null Constraint
In this example, we will set not-null Constraint on the column product_number of the table PRODUCTS_DETAIL.
Command:
Output:
4. Alter Column to Drop not-null Constraint
In this example, we will drop the not-null Constraint column product_number from the table PRODUCTS_DETAIL.
Command:
Output:
5. Alter Table Add Primary Key
In this example, we will add the primary key on column product_key for the table name PRODUCTS_DETAIL.
Command:
Output:
6. Alter Table Rename Column
In this example, we will use the Alter command to rename an existing column from product_key to productkey.
Command:
Output:
7. Alter Table Rename Existing Table
In this example, we will rename the table name from PRODUCTS_DETAIL to PRODUCTS_INFO.
Command:
Output:
