Often in PostgreSQL you may want to add a new column to a table only if it does not already exist.
You can use the ALTER statement with the following syntax to do so:
ALTER TABLE athletes ADD COLUMN IF NOT EXISTS rebounds INTEGER;
This particular example adds a new column named rebounds with a data type of INTEGER to the table named athletes only if this column does not already exist.
If this column does already exist in the table then you will receive a message stating that the column already exists.
The following example shows how to use this syntax in practice.
Example: How to Add Column if it Does Not Exist in PostgreSQL
Suppose that we create the following table named athletes that contains information about various basketball players:
-- create table named 'athletes'
CREATE TABLE athletes (
athleteID INTEGER PRIMARY KEY,
team TEXT,
points INTEGER,
assists INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 22, 5);
INSERT INTO athletes VALUES (0002, 'Hawks', 30, 12);
INSERT INTO athletes VALUES (0003, 'Kings', 12, 3);
INSERT INTO athletes VALUES (0004, 'Kings', 25, 9);
INSERT INTO athletes VALUES (0005, 'Celtics', 10, 5);
INSERT INTO athletes VALUES (0006, 'Nuggets', 14, 13);
INSERT INTO athletes VALUES (0007, 'Nuggets', 20, 8);
INSERT INTO athletes VALUES (0008, 'Nuggets', 26, 3);
-- view all rows in table
SELECT * FROM athletes;
athleteid | team | points | assists
-----------+---------+--------+---------
1 | Mavs | 22 | 5
2 | Mavs | 30 | 12
3 | Cavs | 12 | 3
4 | Heat | 25 | 9
5 | Mavs | 30 | 14
6 | Thunder | 16 | 3
7 | Cavs | 10 | 5
8 | Lakers | 14 | 13
9 | Celtics | 20 | 8
10 | Lakers | 26 | 3
We can see that this table contains four columns: athleteid, team, points and assists.
Suppose that we attempt to add a new column to the table named points:
ALTER TABLE athletes ADD COLUMN IF NOT EXISTS points INTEGER;
psql:commands.sql:24: NOTICE: column "points" of relation "athletes"
already exists, skipping
We receive a message that the column “points” already exists in the table named “athletes” and thus will be skipped.
Suppose instead that we attempt to add a new column to the table named rebounds:
ALTER TABLE athletes ADD COLUMN IF NOT EXISTS rebounds INTEGER;
-- view all rows in table
SELECT * FROM athletes;
athleteid | team | points | assists | rebounds
-----------+---------+--------+---------+----------
1 | Mavs | 22 | 5 |
2 | Mavs | 30 | 12 |
3 | Cavs | 12 | 3 |
4 | Heat | 25 | 9 |
5 | Mavs | 30 | 14 |
6 | Thunder | 16 | 3 |
7 | Cavs | 10 | 5 |
8 | Lakers | 14 | 13 |
9 | Celtics | 20 | 8 |
10 | Lakers | 26 | 3 |
Notice that when we use the SELECT statement to view the updated table we can see that a new column named rebounds has been added because it did not already exist.
Feel free to use similar syntax to add a new column to your own table only if it does not already exist.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Find Duplicate Records in a Table
PostgreSQL: Count Occurrences of Each Value in Column
PostgreSQL: Count Number of Occurrences of Character in String
