Prerequisites
If you are heading towards an operation on a table in Postgres databases, then the following instances are required in this regard.
– Postgres database and a few tables
– (Optional): There must be some columns inside the table to verify that the content inside tables remains the same after renaming the table.
How to rename a table in Postgres
The ALTER TABLE statement of PostgreSQL is used for renaming a table. Although the functionality of ALTER TABLE command is quite extensive due to the functions supported by it. However, in this post the RENAME option of the ALTER TABLE command will be practiced. The RENAME parameter can be used to change the name of tables and columns both. To rename a table, the following syntax would assist you:
The table-name refers to the name of the table you want to change, and the new-table-name indicates the new table name that you want to set.
As we progress through the examples, we will implement the ALTER TABLE statement to rename tables in Postgres. Firstly, connect to the database where the table resides. We are now connected to linuxhint database and to check the available tables we have executed the following command. The output shows there are two tables in our linuxhint database.
Example 1: Rename a table
Referring to the output shown above, let’s say we want to rename the table named employee to employees_data. Before renaming the table, we will check the content of the employee table so that it can be compared after renaming the table. To do so, use the command provided below:
After getting the content, we move on to renaming the employee table.
The ALTER TABLE statement provided below would aid in this regard. After successful execution of the ALTER TABLE statement, we have executed the \dt command, and the output indicates that the employee table has been renamed to employees_data.
After that, you can verify the content of table empolyees_data by using the command stated below, and the content is the same as it was in the old table. Hence, it is concluded that the rename table does not manipulate the data inside a table.
Example 2: Using ALTER TABLE with IF EXISTS
What if the table name you want to rename does not exist? Let’s check the behavior of the ALTER TABLE command in this regard. The command provided below tries to rename the table named store that does not exist in the linuxhint database. Consequently, you would receive an error message saying that the table you were trying to modify does not exist.
Moreover, if we execute the same command with IF EXISTS option then instead of reporting an error, it will initiate a NOTICE that the required relation does not exist.
Conclusion
Postgres is a widely used open-source database management system that is well known for its scalability, security, reliability, and stability. This article demonstrates the way to rename the table in Postgres. For that, Postgres supports the ALTER TABLE command with the RENAME option. It is observed that the ALTER TABLE updates the table’s name and all the dependencies associated with that table are also updated. Moreover, it does not allow you to change the names of multiple tables at once. For this, you must run the command separately for each table.
