How to Add a Column to a PostgreSQL Table

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

When you need to make changes to an existing PostgreSQL table, you’ll need to use the ALTER TABLE statement to get the job done. You can use the ADD COLUMN clause in the ALTER TABLE statement to add a new column to an existing table. In this article, we’ll take a closer look at the ADD COLUMN clause and review some examples of how to use it to add a column in PostgreSQL.

Prerequisites

Before attempting any of the examples in this tutorial, make sure you have PostgreSQL installed on your machine. We’ll be using psql, the command-line interface for PostgreSQL, to modify our table and add columns to it. It’s also important to have some basic knowledge of PostgreSQL in order to follow some of the instructions that will be presented in the article.

Use the PostgreSQL ADD COLUMN clause in an ALTER TABLE statement

Let’s begin by looking at the basic syntax of the ALTER TABLE statement with an ADD COLUMN clause:

1
2
ALTER TABLE TABLE_NAME
ADD COLUMN column_name [data_type];

If you look more closely at this syntax, you’ll see that we specify the name of the table right after the ALTER TABLE statement. This is the table to which we’ll be adding a column. We then indicate the name of the new column and its data type after the ADD COLUMN clause.

Use PostgreSQL to add column example

We’ll need to create a sample dataset to use in our examples. Let’s create a table named clients. This table will have only two columns in it: client_id and client_name. Here’s the statement we’ll use to create the table:

1
2
3
4
CREATE TABLE clients(
    client_id INT PRIMARY KEY NOT NULL,
    client_name VARCHAR NOT NULL
);

It’s not necessary to populate the table with any records for the purposes of this tutorial. Now that our table is created, we’ll try using the ADD COLUMN syntax to add another column named client_email, We can see the ALTER STATEMENT below:

Alter table to add a column in PostgreSQL example

1
2
ALTER TABLE clients
ADD COLUMN client_email VARCHAR NOT NULL;

You can use the command \d followed by the name of the table you altered to get detailed information about that table. It can be helpful to use this command to check a table’s columns and their data types:

1
2
3
4
5
6
7
8
                      TABLE "public.clients"
    COLUMN    |       TYPE        | Collation | NULLABLE | DEFAULT
--------------+-------------------+-----------+----------+---------
 client_id    | INTEGER           |           | NOT NULL |
 client_name  | CHARACTER VARYING |           | NOT NULL |
 client_email | CHARACTER VARYING |           | NOT NULL |
Indexes:
    "clients_pkey" PRIMARY KEY, btree (client_id)

You can also add multiple columns to a table with a single ALTER TABLE statement. To do this, you just need to place a comma after the first column that you add and then use the ADD COLUMN clause again. In the example shown below, we’ll add two columns, “client_phone” and “client_address”, to our clients table:

1
2
3
ALTER TABLE clients
ADD COLUMN client_phone VARCHAR NOT NULL,
ADD COLUMN client_address VARCHAR NOT NULL;

Let’s look at the information for this table again and see how it changed:

1
2
3
4
5
6
7
8
9
10
                       TABLE "public.clients"
     COLUMN     |       TYPE        | Collation | NULLABLE | DEFAULT
----------------+-------------------+-----------+----------+---------
 client_id      | INTEGER           |           | NOT NULL |
 client_name    | CHARACTER VARYING |           | NOT NULL |
 client_email   | CHARACTER VARYING |           | NOT NULL |
 client_phone   | CHARACTER VARYING |           | NOT NULL |
 client_address | CHARACTER VARYING |           | NOT NULL |
Indexes:
    "clients_pkey" PRIMARY KEY, btree (client_id)

Conclusion

When you’re working with tables in a PostgreSQL database, you may find yourself needing to make changes to those tables. It’s easy to add one or more columns to an existing PostgreSQL table by using the ALTER TABLE statement with an ADD COLUMN clause. In this article, we looked at some examples of using the ALTER TABLE statement to add a column in PostgreSQL. By following along with the examples presented in this tutorial, you’ll be able to add columns and modify tables in your own PostgreSQL environment.

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.