How to Rename the table in PostgreSQL

There can be a case where we wish to rename a table in Postgres. We can achieve this by using the Postgres queries or using the psql. The ALTER statement along with the RENAME clause is used to alter the table to rename it while we can perform the same thing manually in pgAdmin.

This post will cover the methods by which we can rename the table in Postgres.

How to Rename the table in PostgreSQL?

We can rename the table by using the Postgres queries in pgAdmin and psql. We can alter the table to rename it. The ALTER keyword and RENAME keyword can be used together to change the name of the table. The same query can be used to rename the table in pgAdmin and psql. The basic syntax to rename the table can be written as:

ALTER TABLE prev_tab_name RENAME TO New_tab_Name;

Syntax Description

  • The ALTER TABLE command is used to Alter the table.
  • The table’s name that has to be altered is written after the ALTER TABLE command.
  • The RENAME TO statement renames the object(table in this case).
  • The new name you desire to allot to the table is specified after the RENAME TO clause.

Let’s see how we can alter a table using an example.

Example: Rename the Table Using the Postgres Query

Let’s rename the already existing table named “acc_details” to “account_details”. 

The query for this case can be written as:

ALTER TABLE acc_details RENAME TO account_details;

This query will rename the table “acc_details” to the “account_details”. This can be verified from the following output:

The “ALTER TABLE” message as an output shows that the table has been renamed.

The modified name of the table can also be verified from the side panel of pgAdmin. The “acc_details” will be replaced by the “account_details” table. 

We can see that the table’s name has been changed to a new name i.e. it is renamed to a new name.

Note: if you do not see your table renamed try refreshing your tables by right-clicking on the table options.

We can also rename the table using the ALTER IF EXISTS clause.

The ALTER TABLE IF EXISTS only alters the table if it exists otherwise it will just skip the statement without throwing an error. It just raises a notice. When it is used with the RENAME TO clause it will rename the table just in case when the table exists in the database.

For example, if we want to rename the table acc_details, which does not exist in the database as we have already renamed it to account_details, we will write the following query:

ALTER TABLE IF EXISTS acc_details RENAME TO account_details;

As the table, “acc_details” does not exist, the ALTER IF EXISTS  statement will not throw an error, it will just raise an error. The following will be the output for the query:

We can see that the ALTER IF EXISTS clause skipped the statement as the table does not exist. But if we used the ALTER TABLE instead, this would have thrown an error.

Rename the Table Manually in PostgreSQL

In the above section, we have learned to rename a table using SQL queries in PostgreSQL. Now we will see how the user can manually perform a similar thing in Postgres. For this, navigate to the table section available in the pgAdmin’s right pane, search for the desired table, and right-click on it. This will open a drop-down menu for you providing multiple options. Select the “Properties” option from the menu.

This will open a window for you containing the properties of the table like this. 

In the “General” tab, the first field will be the name of the table. We can edit the table’s name from there. After doing so, you can press the “save” button to save the changes applied to the name of the table.

You can verify if the table has been renamed or not from the side panel. Under the “Tables” option of the specific database in which it was created. 

We can observe that the table is renamed from “account_details” to “acc_details”.

Conclusion

We can rename a table by using the ALTER TABLE statement with the RENAME TO clause. These statements will collectively change the name of the already existing table from the name specified after the ALTER TABLE statement to the name specified after the RENAME TO clause. In this article, we have learned to rename a table with an example.