How to Alter a Column in a PostgreSQL Table

Introduction to ‘ALTER TABLE’ in PostgreSQL

When you create a table in PostgreSQL and define the columns you’ll need, there’s no way to predict how requirements may change in the future. You’re likely to encounter situations where you need to alter PostgreSQL table columns in your database. It’s easy to accomplish this task in PostgreSQL using the ALTER TABLE command. In this article, we’ll show you how to add and modify columns in an existing PostgreSQL table.

Prerequisites to using PostgreSQL

In order to follow along with the SQL examples we’ll be showing in this article, a few prerequisites must be in place:

• PostgreSQL needs to be installed. You can use the service postgresql status command to confirm that the status is active and then press CTRL + C to exit.

• The psql interface also needs to be installed. You can confirm that it’s installed and working properly by typing psql -V on the command line.

PostgreSQL Keywords Used to Modify Tables

The following is a list of SQL keywords that you can use to modify a PostgreSQL table:

• ADD – Using this keyword with ALTER TABLE allows you to create additional columns in a table.
• MODIFY – Using this keyword with ALTER TABLE allows you to to revise a column and its data type.
• DROP – Use this keyword to delete a column from a table.
• RENAME – Using this keyword with ALTER TABLE allows you to change a column name or table name.
• UPDATE – The UPDATE keyword can be used to change the state of a table, including its columns.

This tutorial will focus on SQL statements that make use of the ALTER TABLE keywords.

Create a PostgreSQL Database for the Table

The first thing we’re going to do is create a database in PostgreSQL. Windows users will need to use the command prompt; Mac and Linux users will use the terminal. Enter into the psql interface using the command shown below:

Once yo’re in the psql interface, use the following SQL statement to create the database:

Creating a Table in a PostgreSQL Database

The syntax for the SQL command used in psql to create a table is shown below:

Here’s an example of how we can use this command to create a sample table named employee:

Granting a User Privileges to a PostgreSQL Table

You can use the following SQL statement to grant privileges to create a PostgreSQL table in a database:

The command \l will show you the list of all the databases that are already in PostgreSQL. Choose one of the databases and then connect to it using the \c command, followed by the database name. An example of this command is shown below:

For the purposes of this tutorial, we’re going to use the database that we created earlier: \c testdb;

Using the ‘ALTER TABLE’ statement to change a PostgreSQL Table

Now that we’ve created a database and a table, let’s see how we can alter that table.

The following SQL statement uses the ALTER TABLE command to add a TIMESTAMP column to the table:

NOTE You can actually insert multiple columns in a table if needed.

Adding a ‘TIMESTAMP’ Column to an Existing PostgreSQL Table

Let’s try adding a timestamp column to our existing employee table:

Return a List of the PostgreSQL Table’s Columns

Next, we’ll use the \d+ command to return all the column names. This will help us verify that the ALTER TABLE command worked properly:

The command shown above should return a table response that includes the new column and its datatype:

Using the ‘ALTER TABLE’ Command to Modify a Column to the PostgreSQL Table

In the previous example, we gave our join_date column a TIMESTAMP datatype. What if we wanted to modify it and change the column’s datatype to DATE? The following SQL statement uses the ALTER TABLE command to change the column type to DATE:

NOTE It’s possible to modify multiple columns in a table.

This command should return a table response that indicates the new column datatype. It should look like this:

As you can see, the TIMESTAMP datatype that we defined earlier has been changed to a DATE datatype.

Conclusion

When you’re working with data in PostgreSQL, it’s important to remember that table definitions aren’t set in stone. You can add and modify columns in existing tables using simple SQL statements. In this article, we showed how to use the ALTER TABLE command to alter PostgreSQL table columns. Using the examples we discussed in this tutorial, you’ll be able to make changes to your own existing PostgreSQL tables as needs and requirements evolve.