How to Rename Column in PostgreSQL

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

How to Rename Column in PostgreSQL

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

Method 1: Using pgAdmin GUI

To rename column in PostgreSQL, follow the steps below.

1. Navigate to your table in pgAdmin
2. Expand the Columns node and right-click on the column name, then select the Properties option, as shown in the screenshot below.

3. Change the column name in the Properties panel and then click ‘Save’ to apply your changes, as shown in the screenshot below.

The column name has been changed successfully, as shown below.

Method 2: Using ALTER TABLE Command

We can also use the following syntax:

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

• ALTER TABLE: This command changes the properties of a specific table.
• table_name: Name of the tables whose column name will be renamed.
• RENAME: This is a clause used with ALTER TABLE to change the name of a column.
• 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 following 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 it 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. While checking, you find that 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 particularly descriptive, you want to give the new column the name ‘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 the RENAME COLUMN clause within the THEN section.

Now, view the table to see the changes.

SELECT * FROM users;

Refer to the above output screenshot, where the ‘name’ column in the ‘users’ table has been renamed to ‘username’.

Method-3 Query to Rename Multiple Columns

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 you want to change the column names ’email’ and ‘password’ of the’users’ table to ‘user_email’ and ‘user_password’.

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

Now, execute the command below to rename the column 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 by checking the existence of the ’email’ column in the ‘users’ table using the IF EXISTS clause. When the condition becomes true, the command within the THEN section is executed, and the column is renamed 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 output above, the column ‘password’ has been renamed to ‘user_password’.The code begins by checking the existence of the ‘password’ column in the ‘users’ table using the IF EXISTS clause. If the condition is true, the command within the THEN section is executed, and the column is renamed 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 it exists in PostgreSQL, where you rename the column name after checking the existence of the column in a table. Also, learn how to check the existence of multiple columns in a table before renaming them.

Renaming columns in PostgreSQL is a straightforward process with the ALTER TABLE RENAME COLUMN syntax and even with a GUI approach, as mentioned in this article.

You may also read:

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