How to rename a table in Postgres

The Postgres database management system includes both object-oriented and relational characteristics and therefore is an object-oriented relational database system. It does support several distinctive features like user defined data types, views, rules, and many more that make Postgres an advanced level DBMS. Like other DBMSs, the primary components of its database are tables and columns. This article provides a procedural guide to rename an existing table of a Postgres database.


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:

> ALTER TABLE <table-name> RENAME TO <new-table-name>

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.

# \dt

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:

# SELECT * FROM employee;

A picture containing text

Description automatically generated

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.

# ALTER TABLE employee RENAME 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.

# SELECT * FROM employees_data;


Description automatically generated with medium confidence

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.

# ALTER TABLE store RENAME TO items;

Graphical user interface

Description automatically generated

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.



Description automatically generated with low confidence


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.

About the author

Adnan Shabbir