PostgreSQL provides different commands/statements to perform the different operations on the Postgres tables. For instance, the CREATE TABLE statement creates a table, the DROP TABLE deletes a table, and the ALTER TABLE updates an existing table. A Postgres table can be renamed using the ALTER TABLE command.

This blog post will teach you how to rename a table in Postgres through practical examples. So, let’s get started!

How to Rename a Table in Postgres?

To rename a particular table in PostgreSQL, use the RENAME TO clause in conjunction with the ALTER TABLE statement:

ALTER TABLE tab_name
RENAME TO new_tab_name;

In the above syntax:

- tab_name represents a table to be renamed.
- new_tab_name represents a new/modified name of the targeted table.

IF EXISTS Option

Postgres experts prefer to use the IF EXISTS option with the ALTER TABLE statement to avoid the “table does not exist” error:

ALTER TABLE IF EXISTS tab_name
RENAME TO new_tab_name;

The IF EXISTS option is used with the ALTER TABLE command in the above syntax. Consequently, if a table with the specified name does not exist, a notice will be issued rather than an error.

Key Points

The following points will assist Postgres users in understanding the "ALTER TABLE RENAME TO" statement in a better way:

- You can’t rename multiple tables in one go. To rename more than one table, you must execute the “ALTER TABLE RENAME TO” statement several times. In simple terms, each table will be renamed one by one.
- Use the IF EXISTS option to avoid the “table does not exist” error.
- PostgreSQL automatically updates its dependent objects when you rename a table, such as foreign key constraints, views, etc.

For a profound understanding, let’s implement the “ALTER TABLE RENAME TO” statement practically.

Example: How Do I Rename a Table in Postgres?

Follow the below provided stepwise instructions to rename a table in Postgres:

Step 1: Create Table

First, we will create a sample Postgres table named “employee_data” with three columns: employee_id, employee_name, and employee_age:

CREATE TABLE employee_data(
employee_id INT PRIMARY KEY,
employee_name TEXT,
employee_age SMALLINT);

The “CREATE TABLE” message in the output shows that the specified command was executed successfully.

Step 2: Verify Table Creation

To verify the table creation, execute the “\dt” command:

\dt;

The “employee_data” table has been created successfully.

Step 3: Rename Table

Suppose we want to rename the “employee_data” table to “staff_data”. To do that, execute the “ALTER TABLE RENAME TO” statement as follows:

ALTER TABLE employee_data 
RENAME TO staff_data;

The “ALTER TABLE” message in the output proves that the specified command was executed successfully.

Step 4: Verify Table Alteration

To verify if the table has been renamed or not, execute the “\dt” command:

\dt;

The above snippet shows that the “employee_data” has been renamed to “staff_data” successfully.

In this way, you can rename any table in PostgreSQL.

Conclusion

To rename a particular table in PostgreSQL, use the RENAME TO clause in conjunction with the ALTER TABLE statement. You can’t rename multiple tables simultaneously. To do so, you must execute the “ALTER TABLE RENAME TO” statement several times. PostgreSQL automatically updates its dependent objects when you rename a table, such as foreign key constraints, views, etc. In Postgres, you can use the IF EXISTS option with the ALTER TABLE command to avoid the “table does not exist” error. This blog post demonstrated how the "ALTER TABLE RENAME TO" statement works in Postgres by providing practical examples.