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 isactive
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 typingpsql -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 withALTER TABLE
allows you to create additional columns in a table.MODIFY
– Using this keyword withALTER 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 withALTER TABLE
allows you to change a column name or table name.UPDATE
– TheUPDATE
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:
1 | psql |
Once yo’re in the psql
interface, use the following SQL statement to create the database:
1 | postgres=# CREATE DATABASE testdb; |
Creating a Table in a PostgreSQL Database
The syntax for the SQL command used in psql
to create a table is shown below:
1 | postgres=# CREATE TABLE {TABLE_NAME} {column_name + data_type + constraints (IF any)}; |
Here’s an example of how we can use this command to create a sample table named employee
:
1 | CREATE TABLE employee(id INT NOT NULL PRIMARY KEY, emp_name text, emp_address CHAR(50), emp_contact INT NOT NULL, salary REAL); |
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:
1 | GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO someUser |
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:
1 | \c 'database_name' |
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:
1 2 | ALTER TABLE TABLE_NAME 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 employee
table:
1 | ALTER TABLE employee ADD join_date TIMESTAMP; |
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:
1 | \d+ employee |
The command shown above should return a table response that includes the new column and its datatype:
1 | join_date | TIMESTAMP WITHOUT TIME zone |
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
:
1 | ALTER TABLE TABLE_NAME ALTER COLUMN column_name TYPE 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:
1 | join_date | DATE |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started