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.


The syntax of the PostgreSQL Alter table command is as mentioned below.

ALTER TABLE [ IF EXISTS ] [ ONLY ] NAME [ * ] action [, ... ]


postgres=# ALTER TABLE products_detail ADD COLUMN product_catagory varchar(30);
postgres=# \d products_detail


We can see in the below output that the column product_catagory has been added to the table name products_detail.

postgresql alter command add column command

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.


postgres=# ALTER TABLE products_detail DROP COLUMN product_catagory;


postgresql alter table drop column command

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.


postgres=# ALTER TABLE products_detail ALTER COLUMN product_number SET not NULL;


postgresql alter column add not null command

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.


postgres=# ALTER TABLE products_detail ALTER COLUMN product_number DROP not NULL;


postgresql alter column drop not null command

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.


postgres=# ALTER TABLE products_detail ADD CONSTRAINT primary_key PRIMARY KEY (product_key);


postgresql add primary key command

6. Alter Table Rename Column

In this example, we will use the Alter command to rename an existing column from product_key to productkey.


postgres=# ALTER TABLE products_detail rename COLUMN product_key TO productkey;


postgresql rename column command

7. Alter Table Rename Existing Table

In this example, we will rename the table name from PRODUCTS_DETAIL to PRODUCTS_INFO.


postgres=# ALTER TABLE products_detail rename TO products_info;


postgresql rename table command