PostgreSQL: How to Rename Column Only if Exists

Often in PostgreSQL you may want to rename a column in a table only if it exists.

You can use the following syntax to do so:

DO $$
    BEGIN
        ALTER TABLE athletes RENAME COLUMN assists TO total_assists;
    EXCEPTION
        WHEN undefined_column THEN RAISE NOTICE 'Column does not exist';
END $$;

This particular example attempts to rename the column assists to total_assists.

If this column does not exist then an error message will appear that states Column does not exist.

The following example shows how to use this syntax in practice.

Example: How to Rename Column Only if it Exists 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 INT
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 15);
INSERT INTO athletes VALUES (0002, 'Mavs', 35);
INSERT INTO athletes VALUES (0003, 'Mavs', 22);
INSERT INTO athletes VALUES (0004, 'Mavs', 18);
INSERT INTO athletes VALUES (0005, 'Hawks', 19);
INSERT INTO athletes VALUES (0006, 'Hawks', 15);
INSERT INTO athletes VALUES (0007, 'Hawks', 17);
INSERT INTO athletes VALUES (0008, 'Hawks', 24);

-- view all rows in table
SELECT * FROM athletes;

 athleteid | team  | points 
-----------+-------+--------
         1 | Mavs  |     15
         2 | Mavs  |     35
         3 | Mavs  |     22
         4 | Mavs  |     18
         5 | Hawks |     19
         6 | Hawks |     15
         7 | Hawks |     17
         8 | Hawks |     24

Suppose that we would like to rename the team column to team_name only if it exists.

We can use the following syntax to do so:

-- attempt to rename 'team' to 'team_name'
DO $$
    BEGIN
        ALTER TABLE athletes RENAME COLUMN assists TO total_assists;
    EXCEPTION
        WHEN undefined_column THEN RAISE NOTICE 'Column does not exist';
END $$;

-- view updated table
SELECT * FROM athletes;

 athleteid | team_name | points 
-----------+-----------+--------
         1 | Mavs      |     15
         2 | Mavs      |     35
         3 | Mavs      |     22
         4 | Mavs      |     18
         5 | Hawks     |     19
         6 | Hawks     |     15
         7 | Hawks     |     17
         8 | Hawks     |     24

Since the team column did exist, we can see that it was successfully renamed to team_name.

Now suppose that we attempt to rename a column called assists to total_assists:

DO $$
    BEGIN
        ALTER TABLE athletes RENAME COLUMN assists TO total_assists;
    EXCEPTION
        WHEN undefined_column THEN RAISE NOTICE 'Column does not exist';
END $$;

psql:commands.sql:26: NOTICE:  Column does not exist

Since the assists column did not exist in the table, we received the message Column does not exist.

You can use similar syntax to rename a column only if it exists in your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Count Occurrences of Each Value in Column
PostgreSQL: How to Update Multiple Columns in Table
PostgreSQL: How to Append String to Column Value