The "RENAME TABLE" statement is typically used in MySQL to rename/retitle a table. In PostgreSQL, the same functionality is achieved using the ALTER TABLE command. The ALTER TABLE command serves multiple functionalities regarding table modification, such as dropping a table column, renaming a table/column, etc. For this purpose, different clauses are used along with the ALTER TABLE command. To rename an already existing table, you must use the RENAME clause within the ALTER TABLE statement.
This write-up will explain how to rename a table using the ALTER TABLE statement. So, let’s start!
How to Use RENAME TABLE Statement in PostgreSQL
In PostgreSQL, the RENAME TO clause is used with the below syntax to rename a specific table:
ALTER TABLE old_Name RENAME TO new_Name;
- ALTER TABLE is a statement that is used to alter a table.
- Old_name represents the table’s original/existing name.
- RENAME TO is a clause in PostgreSQL that is used to RENAME a table.
- New_name represents the table’s new/altered name.
All in all, on successful execution of the above-given query, the table’s old_Name will be renamed to new_Name.
Example: How to Use RENAME TO Clause in PostgreSQL
Open the SQL SHELL, connect to a database of your choice, and perform the following steps to rename a table:
Step 1: Check the Available Tables in the Selected Database Using \dt Command
Run the below-mentioned command in the SQL SHELL to see the list of available tables in the selected database i.e. “example”:
\dt;
The output of the \dt command shows that there are 9 tables available in the example database.
Step 2: Rename the Selected Table Using RENAME TO Clause
Let’s say we need to rename the “team_info” table to “team_details”. Simply run the following query to rename a table in PostgreSQL:
ALTER TABLE team_info RENAME TO team_details;
The above snippet shows that the selected table has been altered successfully.
Step 3: Verify the Table Alteration Using \dt Command
Run the \dt command to see whether the selected table has been renamed or not:
\dt;
The output verified that the team_info table had been successfully renamed to team_details.
Step 4: Verify the Table Content/Record Using SELECT Command
You can execute the below-given command to see whether renaming the table affects the table’s content or not:
SELECT * FROM team_details;
The output verified that the RENAME TO clause doesn’t affect the internal structure of the selected table.
RENAME TO Clause With IF EXISTS Parameter
In PostgreSQL, trying to RENAME a table that does not exist will throw an error. To avoid such an error, the “IF EXISTS” parameter is used with the ALTER TABLE statement. The modified syntax will be as follows:
ALTER TABLE IF EXISTS old_Name RENAME TO new_Name;
Now, firstly, the ALTER TABLE command will check whether the specified table exists or not. If it exists, then further process will take place. If the specified table doesn’t exist, then PostgreSQL will show a notice instead of throwing an error.
What is the Need for the “IF EXISTS” Parameter?
Suppose someone tries to access a table that doesn’t exist in the targeted database, then in such a case, PostgreSQL will throw an error:
ALTER TABLE car_details RENAME TO vehicle_details;
Solution: Use the “IF EXISTS” Parameter
Let’s modify the above example a little bit and use the IF EXISTS parameter to avoid the “relation/table not exist” error:
ALTER TABLE IF EXISTS car_details RENAME TO vehicle_details;
The output verified that this time we got a notice instead of an error. This way, the normal flow of the query wouldn't be affected. In case the table doesn't exist, the ALTER TABLE statement will be skipped and the control will be moved/transferred to the next statement.
How to RENAME Dependent Tables in PostgreSQL
Suppose we have two tables that are dependent on each other via foreign key constraints, and their details are as follows:
Renaming a table will automatically modify the dependent object like a foreign key constraint. For example, the below code renames the "emp_team" table to "e_team":
ALTER TABLE emp_team RENAME TO e_team;
Now, let's describe both tables one more time, and you will witness that changes made in one table are also implemented in the other table:
That's all about renaming a table Postgres using RENAME TO clause.
Conclusion
In PostgreSQL, the RENAME TO clause is used with the collaboration of the ALTER TABLE statement to rename an already existing table. Trying to rename a table that doesn’t exist will throw an error; however, the IF EXISTS parameter can be used to avoid such errors. This write-up has explained how to rename a table in PostgreSQL using different examples.
