PostgreSQL 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:

In the above syntax, we have the following parameters:

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:

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:

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:

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:

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:

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:

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:

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:

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.

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:

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:

Output

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