ADD Columns

In this section, we are going to understand how the PostgreSQL ADD COLUMN Command is used to add one or more columns to the current database table.

PostgreSQL ADD COLUMN command

We will use the alter table add column, command for adding the new column to an existing table.

Syntax

The syntax of the Alter add column command is given below:

ALTER TABLE table_name  

ADD COLUMN new_column_name data_type; 

In the above syntax, we have the following parameters:

Parameter	Description
Alter table	It is a clause, which is used to modify the definition of a table.
Table_name	It is used to describe the table name where we need to add a new column after the ALTER TABLE clause.
New_cloumn _name	It is used to specify the column name with its attribute like default value, data type, and so on, after the ADD COLUMN condition.

Note: When we add a new column to the table, PostgreSQL enhances it at the end of the table because PostgreSQL has no other choice to define he new column’s place in the table.

Add multiple columns using alter table command

Syntax of adding the multiple columns by using the alter table command:

ALTER TABLE table_name  

ADD COLUMN new_column_name_1 data_type constraint,  

ADD COLUMN new_column_name_2 data_type constraint,  

:::  

:::  

ADD COLUMN new_column_name_N data_type constraint;

Examples of PostgreSQL ADD COLUMN

For our better understanding of adding the columns, we will follow the below process:

Firstly, we will create a new table named as Persons with two columns Person_id and First_name with the help of below command:

CREATE TABLE Persons (  

    Person_ID Serial primary key,  

   Person_Name varchar not Null       

 );

Output

After executing the above query, we will get the below message window, which says that the Persons table has been created:

After that, we will add the Address column to the Persons table with the below command’s help:

ALTER TABLE Persons   

ADD COLUMN Address VARCHAR; 

Output

After executing the above query, we will get the below message window:

And the below command will help us to add the City and phone_no columns to the Persons table:

ALTER TABLE Persons   

ADD COLUMN City VARCHAR,  

ADD COLUMN Phone_no VARCHAR; 

Output

After executing the above query, we will get the below message window:

If we want to see the Persons table structure in the psql, we will use the below command:

javatpoint=# \d Persons                                    

The output of the Persons table structure

As we can see in the above image, we have the Address, City, and Phone_no columns added at the end of the column list of the Persons table.

Adding a column with the NOT NULL Constraint

In this, we will be adding a column with not null constraints to a particular table that contains some data.

Firstly, we will be inserting some data into the Persons table by using Insert command:

INSERT INTO Persons (Person_name)  

VALUES  

    ('John'),  

    ('Emily'),  

    ('Mike'); 

Output

We can see that the three rows have been inserted in the Persons table in the below message window.

Now, let us assume that we wanted to add the Email column to the Persons table, and for this, we use the following command:

ALTER TABLE Persons   

ADD COLUMN Email VARCHAR NOT NULL; 

Output

Once we execute the above command, we will get one error which says that
column “email” contains null values

The above error has occurred because the Email column contains the NOT NULL constraint.

In PostgreSQL, the new column takes the NULL value when adding the columns, which also disrupts the NOT NULL constraint.

To resolve the above error, we need to follow the below steps:

Step1

Firstly, we need to add the column without the NOT NULL constraint, and for this, we will use the following command:

ALTER TABLE Persons   

ADD COLUMN Email VARCHAR; 

Output

As we can see in the below message window that Email column has been added into the Persons table:

Step2

Now, we will update the values for the Email column.

UPDATE Persons  

SET Email = '[email protected]'  

WHERE  

Person_id = 1;  

  

UPDATE Persons  

SET Email = '[email protected]'  

WHERE  

Person_id = 2;  

  

UPDATE Persons  

SET Email = '[email protected]'  

WHERE  

Person_id = 3; 

Output

As we can see in the below message window that Email column values have been updated into the Persons table:

If we want to check that the values are updated or not in the Persons table, we will use the Select command:

Select * From Persons;  

Output

The output of the above query is as below:

Step3

After updating the Email column value, we will set the NOT NULL constraint for the Email column into the Persons table by using the following command:

ALTER TABLE Persons  

ALTER COLUMN Email SET NOT NULL;

Output

After executing the above command, we will get the below message window: