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 statuscommand to confirm that the status is
activeand then press CTRL + C to exit.
psqlinterface also needs to be installed. You can confirm that it’s installed and working properly by typing
psql -Von 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 TABLEallows you to create additional columns in a table.
MODIFY– Using this keyword with
ALTER TABLEallows 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 TABLEallows you to change a column name or table name.
UPDATEkeyword 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
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:
\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:
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:
ADD new_column_name TIMESTAMP;
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
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
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
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started