PostgreSQL Rename Tables

Sometimes, you want to modify the name of an existing table for some reason, for example:

• You used a wrong table name when creating the table.
• You need to change the table name to something more meaningful.
• Because the requirements of the product was changed, and the table name needs to be changed to accommodate the new business.
• Your team uses a new naming convention, and you need to rename tables that do not conform to the new convention.

In PostgreSQL, you can use the ALTER TABLE statement to rename a table.

PostgreSQL rename table syntax

To rename a table, use the ALTER TABLE statement as follows:

ALTER TABLE [IF EXISTS] table_name
RENAME TO new_table_name;

Explanation:

• The table_name is the table to be renamed.
• The new_table_name is the new name of table.
• The IF EXISTS option is used to avoid errors due to table names entered that do not exist. It is optional.

When you rename a table, PostgreSQL automatically updates those objects that depend on the table.

PostgreSQL Rename tables Examples

This example demonstrates how to rename a table in PostgreSQL.

We will create two tables users and user_hobbies in the testdb database. Among them, the users table is used to store the user’s name, gender, age and other information, and the user_hobbies table is used to store the user’s hobbies.

The following statement creates the table named users:

CREATE TABLE users (
  user_id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(45) NOT NULL,
  age INTEGER,
  locked BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMP NOT NULL
);

The following statement creates the user_hobbies table:

CREATE TABLE user_hobbies (
  hobby_id SERIAL NOT NULL,
  user_id INTEGER NOT NULL,
  hobby VARCHAR(45) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  PRIMARY KEY (hobby_id),
  CONSTRAINT fk_user
    FOREIGN KEY (user_id)
    REFERENCES users (user_id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT);

To show the definition of a user_hobbies table, use the \d command:

\d user_hobbies;

                                          Table "public.user_hobbies"
  Column   |            Type             | Collation | Nullable |                    Default
------------+-----------------------------+-----------+----------+------------------------------------------------
hobby_id   | integer                     |           | not null | nextval('user_hobbies_hobby_id_seq'::regclass)
user_id    | integer                     |           | not null |
hobby      | character varying(45)       |           | not null |
created_at | timestamp without time zone |           | not null |
Indexes:
   "user_hobbies_pkey" PRIMARY KEY, btree (hobby_id)
Foreign-key constraints:
   "fk_user" FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE RESTRICT ON DELETE CASCADE

Here, the user_hobbies table has a foreign key that references the users table.

Suppose, your team made a new naming convention, all tables’ names need to start with t_, so you need to rename the users table to t_users, rename the user_hobbies table to t_user_hobbies.

To rename the users table to t_users, use the following statement:

ALTER TABLE users RENAME TO t_users;

Use the \d command show the definition of the user_hobbies table:

\d user_hobbies;

                                          Table "public.user_hobbies"
  Column   |            Type             | Collation | Nullable |                    Default
------------+-----------------------------+-----------+----------+------------------------------------------------
hobby_id   | integer                     |           | not null | nextval('user_hobbies_hobby_id_seq'::regclass)
user_id    | integer                     |           | not null |
hobby      | character varying(45)       |           | not null |
created_at | timestamp without time zone |           | not null |
Indexes:
   "user_hobbies_pkey" PRIMARY KEY, btree (hobby_id)
Foreign-key constraints:
   "fk_user" FOREIGN KEY (user_id) REFERENCES t_users(user_id) ON UPDATE RESTRICT ON DELETE CASCADE

It is clear from the output that the foreign key constraints have been updated and reference the t_users table.

To rename the user_hobbies table to t_user_hobbies, use the following statement:

ALTER TABLE user_hobbies RENAME TO t_user_hobbies;

Finally, use the \dt command to show all the tables in the current database to verify the results:

\dt

            List of relations
Schema |      Name      | Type  |  Owner
--------+----------------+-------+----------
public | t_user_hobbies | table | postgres
public | t_users        | table | postgres
(2 rows)

Conclusion

In PostgreSQL, you can use the ALTER TABLE ... RENAME TO statement to rename a table.