In PostgreSQL, the ADD COLUMN command along with the ALTER TABLE clause is used to add/insert one or more than one column to an existing table. The ADD COLUMN statement enables us to add constraints to a column in PostgreSQL, such as NOT NULL,UNIQUE, etc. So, let’s learn how the ADD COLUMN statement works in PostgreSQL with the help of some examples.

How to Add/Insert a New Column to a Table in Postgres?

You have to follow a specific syntax to add a new column to any particular table that already exists:

ALTER TABLE tbl_name  
ADD COLUMN col_name data_type;

Let’s discuss the above-given syntax stepwise:

- ALTER TABLE is a command/clause used to modify/alter a table.

- tbl-name is a user-defined table name.

- ADD COLUMN inserts one or more than one column to a table.

- col_name represents the column to be altered.

- data_type represents the type of targeted column such as integer, character, etc.

Example: How to add a single column to an already existing table?

Let’s execute the ADD COLUMN command from the pgAdmin to learn how it works in PostgreSQL:

Step 1: Select the Desired Table

Firstly, open the pgAdmin, select the database, find the available “Tables” under the “Schemas” section, and select the desired one:

Let’s assume we want to alter the “team_info” table.

Step 2: Open the Query Tool

From the menu bar, select the Query Tool under the Tools tab as shown in the below snippet:

Step 3: Add a New Column

Currently, we have three columns in the “team_info” table. Let’s execute the ADD COLUMN command to add a new column named “team_lead” in the “team_info” table:

ALTER TABLE team_info  
ADD COLUMN team_lead VARCHAR;

Now press F5 or click on the “Execute/Refresh” button to run the ADD COLUMN command:

The output shows that the “team_info” table has been altered successfully.

Step 4: Verify the Working of ADD COLUMN Command

Right-click on the desired table and select the “Refresh” option to see the modifications in the selected table:

Once you click on the “Refresh” option, consequently, the selected table will be updated:

The above-snippet clarified that the “team_lead” column had been added to the “team_info” table successfully.

How to Add More Than One Column to a Table in Postgres?

As we have discussed earlier, the ADD COLUMN command can be used to add multiple columns to any specific table. Now, we will learn it practically.

Syntax

The below-given syntax will be used to add multiple columns to a table in PostgreSQL:

ALTER TABLE tbl_name
ADD COLUMN first_col data_type constraint,
ADD COLUMN second_col data_type constraint, 
...
ADD COLUMN nth-col data_type constraint;

The above snippet shows that we can add multiple columns to a table using comma-separated syntax.

Example: How to Add Multiple Columns to a Particular Table in Postgres?

Follow the below-listed instructions to add multiple columns to a table:

Step 1: Select the Table

Choose a table you want to alter; let’s say we want to modify the “staff_details” table:

The above snippet shows that the “staff_details” table has two columns.

Step 2: Add Multiple Columns

Let’s execute the “ADD COLUMNS” statement to add a couple of more columns in the “staff_details” table:

ALTER TABLE staff_details
ADD COLUMN staff_email VARCHAR,
ADD COLUMN staff_location VARCHAR;

In the above-given query, the ALTER TABLE command is used to alter/modify the “staff_details” table. While the ADD COLUMNcommand is used to add the “staff_email” and “staff_location” columns in the “staff_details” table:

Step 3: Verify the Working of ADD COLUMN Command

Refresh the targeted table and click on the Columns section to see the available columns in the selected table:

The above-snippet verified that the “staff_email” and “staff_location” columns had been added to the “staff_details” table successfully.

How to Add Columns With Constraints in Postgres?

In Postgres, we can add a column with constraints such as NOT NULL, DEFAULT, UNIQUE, etc. Here is the basic syntax of adding a column with constraints in Postgres:

ALTER TABLE tbl_name
ADD COLUMN col_name data_type constraints;

Consider the below-listed points for a detailed understanding of the above-given syntax:

- The ALTER TABLE command modifies a table.

- tbl-name is the table to be altered.

- ADD COLUMN adds/inserts one or more than one column to the targeted table.

- data_type represents the type of targeted column such as integer, character, etc.

- Constraints represent a rule to be implemented on a column.

- col_name represents a column to be added to a table with constraints.

Example: How to Add a Column with constraints in Postgres

Follow the below-listed steps to understand how to insert a new column with a constraint in PostgreSQL:

Step 1: Add DEFAULT Constraints

Let’s suppose we want to add an “is_illegable” column with a default value “false”. To do so, we will utilize the “DEFAULT” constraint as shown in the below-given snippet:

ALTER TABLE team_info
ADD COLUMN is_illegible BOOLEAN default false;

The above-given query will add an “is_illegible” column in the “team_info” table. The “is_illegible” column has a Boolean data type with the default value “false”:

Step 2: Describe the Altered Table

Run the “\d” command from the SQL SHELL (psql) to verify the working of the ADD COLUMN command:

\d team_info;

The output verified that the “is_illegible” column having a data type boolean with a default value false had been added to the “team_info” table.

Conclusion

In PostgreSQL, the ADD COLUMN command/statement along with the ALTER TABLE clause is used to add single or multiple columns to a table. The ADD COLUMN command allows us to add new columns with constraints such as DEFAULT, NOT NULL, UNIQUE, etc. This write-up considered some examples to explain the working of the ADD COLUMN command in a better way.