Postgres ALTER TABLE ADD COLUMN

Introduction

When you’re working with PostgreSQL, you’ll find that you may need to add a column or two to an existing table. This task is made simple with the help of the Postgres ALTER TABLE and ADD COLUMN statements. In this article, we’ll explain how to use these statements to add multiple columns to a PostgreSQL table.

Prerequisites

Let’s take a moment to go over a few prerequisites that need to be in place in order to follow along with the examples shown in this tutorial:

• First, you’ll need to ensure that PostgreSQL server is properly installed and configured. The service must be running in the background.

If you’re working on Linux and Windows systems, you can download PostgreSQL here

• You can start PostgreSQL server in a Linux environment with the command shown below:

• If you’re not sure whether PostgreSQL is running, you can check using the following command:

• The output of this command will look like the following:

• To start, stop and restart PostgreSQL server on a Windows machine, you’ll need to perform the following steps:

1. First, open Control Panel
2. Next, open Administrative Tools
3. Open Services
4. Find the PostgreSQL Server service
5. Finally, stop, start or restart the service

What is the PostgreSQL ‘ALTER TABLE’ statement?

The PostgreSQL ALTER TABLE statement allows you to change the initial definition of a table. The need to modify a table’s definition tends to come up as requirements change during the software development process.

Shown below is the syntax for the PostgreSQL ALTER TABLE statement:

As you can see, this statement’s syntax is fairly straightforward; however, the action part of the statement allows for a variety of options:

• ADD COLUMN — This allows us to add a new table column. This can be used in much the same way as the PostgreSQL CREATE TABLE syntax.
• RENAME — This will change the name of the target column without affecting the data already stored in it.
• DROP COLUMN — This option allows us to remove a single column or multiple columns within the specified table.
• SET DATA TYPE — As the name implies, this option allows us to modify the table column’s data type.

PostgreSQL ‘ADD COLUMN’ Statement

In the previous section, we discussed the PostgreSQL ALTER TABLE statement and looked at the different actions you can take within this statement. Now, we’ll focus our discussion on the ADD COLUMN action.

We can use the ALTER TABLE ADD COLUMN statement to add a new column to the specified table. The syntax for this statement is shown below:

Let’s look at the above statement and discuss each component of it:

• First, we specify the name of the table that we want to modify after the clause ALTER TABLE.
• We then indicate the name of the column and its planned attributes: the desired value, the data type to be used and more. These attributes are defined right after the clause ADD COLUMN.

All newly added columns will be placed at the end of the target table by PostgreSQL, When you make design changes to your database tables, keep in mind that PostgreSQL doesn’t allow you to specify the position of the new column within the target table.

The example shown below depicts a real-life example of adding a column to an existing table.

In our example, we have a table named customer_info, which contains a few records as shown below:

Let’s say we want to add a column named age with a datatype of INT. To do this, we’ll use the following statement.

Now we can perform a SELECT statement to see if we successfully added the age column to the customer_info table.

The output should look like the following:

We can see that PostgreSQL was able to execute the ADD COLUMN statement without affecting the existing data in the table in any way. This SELECT statement confirms that we have successfully added the age column to our customer_info table.

PostgreSQL ‘ADD_COLUMN’ Statement to Add Multiple Columns

Now that we know how to add a single column to an existing table, we can try adding multiple columns.

To do this, we use the following statement:

Let’s see if the above statement successfully altered the table customer_info by using the SELECT statement:

We can see that our addition of multiple columns was executed correctly. Alternatively, we can use the \d command against our target table to get the same results. You can see how this works in the example shown below:

Conclusion

Adding columns to an existing table is a common part of database administration. Some situations may require you to add only one column, but others might require the addition of multiple columns. In this tutorial, we showed how to use the Postgres ALTER TABLE and ADD COLUMN statements to add multiple columns to an existing table. You can use the examples provided in this article as a guide when you add new columns to your own database tables.