How to Perform the Postgres ADD COLUMN IF NOT EXISTS

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

Introduction

In PostgreSQL, the ALTER TABLE statement can be used to add, delete or modify your table. If you want to add a column to a table, you simply specify the ADD COLUMN clause in the ALTER TABLE statement. However, you’ll encounter an error if you attempt to add a column that already exists. It’s easy to avoid this error by using the IF NOT EXISTS option with your ADD COLUMN clause. This option instructs PostgreSQL to add the new column only if the column name does not exist in the table. In this article, we’ll take a closer look at the Postgres ADD COLUMN IF NOT EXISTS command and check out some examples of its use.

Prerequisites

In order to get the most out of this tutorial, you’ll need to make sure a couple of prerequisites are in place:

  • PostgreSQL must be installed on your computer so that you can test out our examples of the Postgres ADD COLUMN IF NOT EXISTS command.
  • You should have some basic knowledge of PostgreSQL in order to follow along with the instructions provided in this article.

The Postgres IF NOT EXISTS syntax

We’ll begin with a quick look at the syntax for the IF NOT EXISTS option in an ALTER TABLE statement:

1
2
ALTER TABLE TABLE_NAME
ADD COLUMN IF NOT EXISTS column_name [ DATA TYPE ]

Let’s discuss this syntax in a bit more detail:

  • First, we specify the name of the table to which we want to add a column.
  • We supply the IF NOT EXISTS option after the ADD COLUMN clause, and then we specify the name of the column and its data type.

The IF NOT EXISTS option will check if the specified column name exists in the table. With this option in place, no error will be returned if you try to add a column that already exists.

Postgres IF NOT EXISTS example

Before we proceed, let’s create a sample table to use in our examples:

1
2
3
4
5
CREATE TABLE employees(
id SERIAL PRIMARY KEY,
name VARCHAR(50),
POSITION VARCHAR(50)
);

The table named employees is now created.

Add column

Now, let’s try to add a new column to our table with the same column name as an existing column. We’ll use the following ALTER TABLE statement:

1
2
ALTER TABLE employees ADD COLUMN name VARCHAR(50);
ERROR:  COLUMN "name" OF relation "employees" already EXISTS

This statement will return an error because the column “name” in the employees table already exists. To avoid this error, we can just use the IF NOT EXISTS option. Let’s see what our statement looks like with this option in place:

1
2
3
ALTER TABLE employees ADD COLUMN IF NOT EXISTS name VARCHAR(50);
NOTICE:  COLUMN "name" OF relation "employees" already EXISTS, skipping
ALTER TABLE

NOTE: The statement above did not return an error, but it did display a message alerting you that a column with the same name already exists.

What do you think will happen if you try to add a column with the same name of an existing column but a different data type? Let’s try it and see:

1
2
3
ALTER TABLE employees ADD COLUMN IF NOT EXISTS name TEXT;
NOTICE:  COLUMN "name" OF relation "employees" already EXISTS, skipping
ALTER TABLE

The column will still not be added because a column with the same name already exists.

Conclusion

When you need to make a change to one of your PostgreSQL tables, it’s easy to add, delete or rename a column using the ALTER TABLE statement; however, an error may occur if you try to add a column that already exists. To sidestep any potential errors, you can use the IF ALREADY EXISTS option after the ADD COLUMN clause in your ALTER TABLE statement. In this article, we discussed the Postgres ADD COLUMN IF ALREADY EXISTS statement and reviewed some examples of how to use it. With our examples and step-by-step instructions, you’ll be able to add columns to a table without encountering errors 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.