PostgreSQL provides a RENAME COLUMN clause that is used with the collaboration of ALTER TABLE command to rename a column. The RENAME COLUMN command allows us to rename a single or multiple columns. PostgreSQL doesn’t provide the “IF EXISTS” option for the “RENAME COLUMN” command.
This write-up will explain the below-listed use-cases of the RENAME COLUMN command in PostgreSQL.
● How to Use RENAME COLUMN Command to Rename Columns in PostgreSQL?
● How to Rename a Column That Has Some Dependent Objects?
● How to Use RENAME COLUMN Command to Rename Several Columns in PostgreSQL?
So, let’s get started.
How to Use RENAME COLUMN Command to Rename Columns in PostgreSQL?
The “RENAME COLUMN” command can also be used as “RENAME”. The RENAME COLUMN command gets executed with the assistance of ALTER TABLE command, as shown in the following syntax:
ALTER TABLE tab_name RENAME COLUMN old_col_name TO new_col_name;
Let’s understand how the above snippet works:
- ALTER TABLE is a clause used to alter or modify a table.
- tab_name is a table to be altered/modified.
- RENAME COLUMN is a command that renames a column.
- old_col_name represents a column to be renamed.
- new_col_name represents new/modified column name.
Example # 1: How to Rename a Table’s Column in Postgres?
Follow the below-given steps to learn how RENAME COLUMN command works in PostgreSQL:
Step 1: Choose a Database
Open the SQL SHELL and establish a connection with a database using the “\c” command:
\c example;
The output shows that we are successfully connected to the “example” database.
Step 2: Select a Table
Run the “\dt” command to check the available tables within the connected database:
\dt;
Select a table of your choice from the available tables.
Step 3: Describe a Table
Let’s run the “\d” command followed by the table name to see all the columns present in the selected table:
\d staff_details;
Step 4: Rename the Column
Suppose we have to rename the “staff_location” column to “staff_address”. Execute the “RENAME COLUMN” command to rename the selected column:
ALTER TABLE staff_details
RENAME COLUMN staff_location TO staff_address;
Step 5: Verify the Column Name
Let’s execute the “\d” command followed by the table name to verify whether the column name has been altered or not:
\d staff_details;
The output verified that the “staff_location” column had been renamed to “staff_address”.
How to Rename a Column That Has Some Dependent Objects?
Renaming a column that has dependent objects such as foreign keys, views, stored procedures, etc., will implement the modifications to its dependent objects as well.
Example: How to Rename a Foreign Key in PostgreSQL?
Consider the below-given steps to rename a column on which some other objects are dependent:
Step 1: Fetch the tables
Firstly, we will fetch the “article_details” and “author_details” tables using the “\d” command:
The above snippet shows that the “article_id” is a foreign key in the “author_details” table. So, renaming the “article_id” column in the “article_details” table will automatically rename the “article_id” column in the “author_details” table.
Step 2: Rename the Column
Let’s run the below-given command to rename the “article_id” column to “id”:
ALTER TABLE article_details RENAME COLUMN article_id TO id;
Step 3: Verify the Column Name
Type “\d” command followed by the table name (i.e., “article_detials”) to see the changes made in the selected table:
\d article_detials;
The above snippet clarified that the “article_id” had been renamed to “id” in the selected table as well as in the dependent objects/tables.
How to Use RENAME COLUMN Command to Rename Several Columns?
Follow the semi-colon separated syntax to rename several columns in PostgreSQL:
ALTER TABLE tab1_name RENAME COLUMN old_col_name TO new_col_name;
ALTER TABLE tab2_name RENAME COLUMN old_col_name TO new_col_name;
Example: How to Rename Several Columns in Postgres?
In this example, firstly, we will rename the “id” column of the “article_details” table to “article_id”. Next, we will modify the name of the “author_id” column to “a_id”:
ALTER TABLE article_details RENAME COLUMN id TO article_id;
ALTER TABLE author_details RENAME COLUMN author_id TO a_id;
Let’s execute this query from the pgAdmin’s query tool:
The output verified that the selected tables had been altered successfully.
Conclusion
PostgreSQL provides a RENAME COLUMN clause that is used with the collaboration of ALTER TABLE command to rename a column. PostgreSQL doesn’t provide the “IF EXISTS” option for the “RENAME COLUMN” command. So, Postgres will throw an error if the targeted column doesn’t exist in the selected table. This write-up considered multiple examples to explain the working of the “RENAME COLUMN” clause.
