How to Add a Column to a PostgreSQL Table

Introduction

When you first create a table in PostgreSQL, you may think you have all the columns you need, but requirements can change over time. You may find that you need to add new columns to an existing table– fortunately, this task is not a difficult one to accomplish using the ALTER TABLE SQL statement. In this article, we’ll show you how to add a column to a PostgreSQL table.

Prerequisites

Since we’ll be executing PostgreSQL statements throughout this tutorial, there are a few prerequisites that are essential for this task:

  • First, you’ll need to ensure that PostgreSQL is already installed on your machine. If you’re not sure whether the service is installed and running, just use the command service postgresql status— this command will let you know if the status is active.

  • You’ll also need to ensure that psql is installed on your machine. You can use the command psql -V to verify that this command-line interface for PostgreSQL is installed and working properly.

Screenshot of a terminal window getting the PostgreSQL server status and psql version

Accessing PostgreSQL using the ‘psql’ Interface

We’ll need to access a database in order to add a column to a table, so the first thing we need to do is connect to PostgreSQL. We can use the command shown below to access a PostgreSQL database on our localhost server using the psql interface:

sudo su - postgres

You’ll need to enter the password for the user. After providing the password and pressing RETURN, you should have access to PostgreSQL.

Then, we’ll use this command to access a particular database:

psql some_username -h 127.0.0.1 -d some_database

You’ll need to enter the user’s password again, and then you’ll be connected to the database specified in the command.

Create a PostgreSQL Database

It’s easy to create a database in PostgreSQL using the psql command-line interface. The SQL statement for creating a database looks like this:

CREATE DATABASE db_name;

NOTE: Using the \l command will return a list of all the databases in PostgreSQL.

For the purposes of our tutorial, we’ll create a database called mydb:

CREATE DATABASE mydb;

NOTE: To connect to a specific database, just type \c followed by the database name, and then enter your query.

Create a PostgreSQL Table

Now that we’ve created a new database, let’s try creating a new table within that database. After entering the database that we just created, we can create a PostgreSQL table by using a statement with the following syntax:

CREATE TABLE TABLE_NAME(COLUMN name + [DATA TYPE] + CONSTRAINTS [OPTIONAL]);

For our example, we’ll create a table with the definition shown below:

CREATE TABLE employee(ID SERIAL, name TEXT, salary REAL);

Using the ‘ADD’ Statement to Add a Column to a PostgreSQL Table

At this point, we’re ready to try adding a column to our PostgreSQL table. We can add a column to an existing table by using the following syntax in psql:

ALTER TABLE TABLE_NAME
ADD COLUMN new_column_name data_type;

As you can see in this statement, we use the ALTER TABLE clause to specify which table we’d like to modify. We then use the ADD COLUMN clause to indicate the new column name, data type and other details.

NOTE: Remember that when you add a column to a table, there’s no option to define the position of the new column; therefore, it’s always appended to the end of the table.

Adding Multiple Columns to a PostgreSQL Table

You can make your job even easier by adding multiple columns to an existing table using a single ALTER TABLE statement. The basic syntax is shown below:

ALTER TABLE TABLE_NAME
ADD COLUMN new_col_1 DATA_TYPE CONSTRAINT[OPTIONAL],
ADD COLUMN new_col_2 DATA_TYPE CONSTRAINT[OPTIONAL],
ADD COLUMN new_col_3 DATA_TYPE CONSTRAINT[OPTIONAL]);

Let’s try adding a single column first:

ALTER TABLE employee
ADD COLUMN age INT;

We’ll get output that looks like this:

TABLE "public.employee"
COLUMN | TYPE | Collation | NULLABLE | DEFAULT
--------+---------+-----------+----------+--------------------------------------
id | INTEGER | | NOT NULL | NEXTVAL('employee_id_seq'::regclass)
name | text | | |
salary | REAL | | |
age | INTEGER | | |
Indexes:
"employee_pkey" PRIMARY KEY, btree (id)

Now we’ll try adding multiple columns:

ALTER TABLE employee
ADD COLUMN phone TEXT,
ADD COLUMN address TEXT,
ADD COLUMN email VARCHAR(50);

The output of this statement will look like the following:

TABLE "public.employee"
COLUMN | TYPE | Collation | NULLABLE | DEFAULT
---------+-----------------------+-----------+----------+--------------------------------------
id | INTEGER | | NOT NULL | NEXTVAL('employee_id_seq'::regclass)
name | text | | |
salary | REAL | | |
age | INTEGER | | |
phone | text | | |
address | text | | |
email | CHARACTER VARYING(50) | | |
Indexes:
"employee_pkey" PRIMARY KEY, btree (id)

Conclusion

When you create a table in PostgreSQL, the initial definition of the table doesn’t need to be set in stone– it’s easy to add one or more columns to an existing table. In this article, we showed you how to use the ALTER TABLE command to add a column in PostgreSQL; we also demonstrated how to add multiple columns in a single SQL statement. Using the instructions and examples we provided, you’ll be able to modify existing tables in your own PostgreSQL database.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.