How to Rename Column If Exists in PostgreSQL

In this PostgreSQL tutorial, I will show you how to rename a column if exists in PostgreSQL, where you will understand how to check the column’s existence in the table before renaming the column.

How to Rename Column If Exists in PostgreSQL

Sometimes as business requirements change, you need to also rename the columns and there can be different reasons behind changing the column names.

To rename a column in PostgreSQL, use the following syntax:

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

• ALTER TABLE: This is the command to change the properties of a specific table.
• table_name: Name of the tables whose column name will be renamed.
• RENAME: It is a clause, used with ALTER TABLE to change the column name.
• old_column_name: This is the name of the old column whose name you want to change.
• TO new_column_name: This is the new name for the old column.

The above syntax is simple, but you want to check the existence of the column before altering the column name and PostgreSQL doesn’t have the built-in option “IF-EXISTS” for the clause RENAME COLUMN.

To check the column before altering its name, you can use the below syntax:

DO $$ 
BEGIN
   IF EXISTS (
      SELECT 1
      FROM   information_schema.columns 
      WHERE  table_name = 'table_name' 
      AND    column_name = 'old_column_name'
   ) THEN
      ALTER TABLE your_table_name RENAME COLUMN old_column_name TO new_column_name;
   END IF;
END $$;

In the above syntax within an anonymous code block,

• The IF EXISTS check for the specific column using the table information_schema.columns by passing the table_name and old_column_name.
• If the particular column exists, then the code of the THEN section is executed to rename the column using the ALTER TABLE statement with the RENAME COLUMN clause.

Let’s see different examples of how to rename the column if exists in PostgreSQL.

Suppose you are a database developer at a software company and you have the task of checking the naming conventions of databases, tables and columns, and while checking you find the column of a table is not descriptive.

The table name is users and it is shown below.

If you look at the column ‘name’ which doesn’t seem more descriptive you want to give the new column name as ‘username’. So for that execute the below command.

DO $$ 
BEGIN
   IF EXISTS (
      SELECT 1
      FROM   information_schema.columns 
      WHERE  table_name = 'users' 
      AND    column_name = 'name'
   ) THEN
      ALTER TABLE users RENAME COLUMN name TO username;
   END IF;
END $$;

In the above code, first, check the existence of the column ‘name’ of the table ‘users’ using the IF EXISTS and then rename the column ‘name’ to ‘username’ using the ALTER TABLE with RENAME COLUM clause within the THEN section.

Now view the table to see the changes.

SELECT * FROM users;

Look at the above output, where the table users column ‘name’ is renamed to ‘username’.

How to Rename Multiple Column If Exists in PostgreSQL

Renaming multiple columns in PostgreSQL is not supported, to rename multiple columns you will have to execute the ALTER TABLE statement with the RENAME COLUMN clause multiple times.

Suppose again you want to change the column email and password of table users to user_email and user_password.

So to change the multiple rename the multiple columns execute the ALTER TABLE command with the RENAME COLUMN clause for each column one by one.

Now execute the below command to rename the column’s name for email.

DO $$ 
BEGIN
   IF EXISTS (
      SELECT 1
      FROM   information_schema.columns 
      WHERE  table_name = 'users' 
      AND    column_name IN ('email')
   ) THEN
      ALTER TABLE users RENAME COLUMN email TO user_email;
   END IF;
END $$;

SELECT * FROM users;

As you can see, the above command renamed the column ‘user’ to ‘user_email’. The code begins with checking the existence of the column ’email’ in table users using the IF EXISTS, when the IF EXISTS condition becomes true, the command within the THEN section is executed and renamed column to ‘user_email’.

Again execute the same command to rename the column name ‘password’.

DO $$ 
BEGIN
   IF EXISTS (
      SELECT 1
      FROM   information_schema.columns 
      WHERE  table_name = 'users' 
      AND    column_name IN ('password')
   ) THEN
      ALTER TABLE users RENAME COLUMN password TO user_password;
   END IF;
END $$;

SELECT * FROM users;

As you can see in the above output, the column ‘password’ is renamed ‘user_password’.The code begins with checking the existence of the column ‘password’ in table users using the IF EXISTS, when the IF EXISTS condition becomes true, the command within the THEN section is executed and renamed column to ‘user_password’.

Now you have successfully renamed the multiple columns of email and password to user_email and user_password.

Conclusion

In this PostgreSQL tutorial, you have covered how to rename a column if exists in PostgreSQL, where you rename the column name after checking the existence of the column in a table. Also, learn how to check multiple column existence in a table before renaming the column.

You may also read:

• PostgreSQL Add Foreign Key If Not Exist
• How to find primary column name in Postgresql
• Postgresql change column data type