PostgreSQL Tutorial: Rename Table

Summary: This tutorial shows you how to rename a table by using the PostgreSQL RENAME table clause of the ALTER TABLE statement.

Table of Contents

Overview of PostgreSQL rename table statement

To rename an existing table, you use the ALTER TABLE statement as follows:

ALTER TABLE table_name
RENAME TO new_table_name;

In this statement:

• First, specify the name of the table which you want to rename after the ALTER TABLE clause.
• Second, give the new table name after the RENAME TO clause.

If you try to rename a table that does not exist, PostgreSQL will issue an error. To avoid this, you add the IF EXISTS option as follows:

ALTER TABLE IF EXISTS table_name
RENAME TO new_table_name;

In this case, if the table_name does not exist, PostgreSQL will issue a notice instead.

To rename multiple tables, you have to execute multiple ALTER TABLE RENAME TO statements. It’s not possible to rename multiple taable using a singe ALTER TABLE statement.

PostgreSQL rename table examples

The following statement creates a new table named vendors :

DROP TABLE IF EXISTS vendors;
CREATE TABLE vendors (
    id serial PRIMARY KEY,
    name VARCHAR NOT NULL
);

To rename the vendors table to suppliers, you use the following ALTER TABLE RENAME TO statement:

ALTER TABLE vendors RENAME TO suppliers;

Suppose each vendor or supplier belongs to a group. To manage this relationship, you need to add the supplier_groups table as follows:

CREATE TABLE supplier_groups (
    id serial PRIMARY KEY,
    name VARCHAR NOT NULL
);

You also need to add a new column to the suppliers table named group_id. This column is the foreign key column that links to the id column of the supplier_groups table:

ALTER TABLE suppliers 
ADD COLUMN group_id INT NOT NULL;

ALTER TABLE suppliers 
ADD FOREIGN KEY (group_id) REFERENCES supplier_groups (id);

To save time querying the complete supplier data, you can create a view against the suppliers and supplier_groups tables like this:

CREATE VIEW supplier_data 
AS SELECT
    s.id,
    s.name,
    g.name  supply_group
FROM
    suppliers s
INNER JOIN supplier_groups g ON g.id = s.group_id;

When you rename a table to the new one, PostgreSQL will automatically update its dependent objects such as foreign key constraints, views, and indexes.

Let’s check the suppliers table first:

\d suppliers

The output shows that the suppliers table has a foreign key constraint which references the supplier_groups table.

Now, you can rename the supplier_groups table to groups as follows:

ALTER TABLE supplier_groups RENAME TO groups;

You can verify the foreign key constraint in the suppliers table by describing the suppliers table as follows:

\d suppliers

As you can see clearly from the output, the foreign key constraint was updated and referenced the groups table instead.

The following statement shows the supplier_data view:

\d+ supplier_data

The output shows that the supplier_groups table in the SELECT statement of the view was also updated to groups table.

In this tutorial, you learned how to rename a table by using the PostgreSQL RENAME table clause of the ALTER TABLE statement.