Summary: this tutorial shows you how to rename a table by using the PostgreSQL RENAME table clause of the ALTER TABLE statement.
Overview of PostgreSQL rename table statement
To rename an existing table, you use the ALTER TABLE statement as follows:
1 2 | 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 TABLEclause. - Second, give the new table name after the
RENAME TOclause.
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:
1 2 | 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 do it in a single statement.
PostgreSQL rename table examples
We create a new table named vendors for the sake of demonstration.
1 2 3 4 | 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:
1 | ALTER TABLE vendors RENAME TO suppliers; |
Suppose each vendor or supplier belongs to a group. To manage this relationship, we need to add the supplier_groups table as follows:
1 2 3 4 | CREATE TABLE supplier_groups ( ID serial PRIMARY KEY, name VARCHAR NOT NULL ); |
We 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.
1 2 3 4 5 | 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 complete supplier data, we create a view against the suppliers and supplier_groups tables as follows:
1 2 3 4 5 6 7 | CREATE VIEW supplier_data AS SELECT s.id, s.name, g.name 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, etc.
Let’s check the suppliers table first:
1 2 3 4 5 6 7 8 9 10 11 | test=# \d suppliers; Table "public.suppliers" Column | Type | Modifiers ----------+-------------------+------------------------------------------------------ id | integer | not null default nextval('vendors_id_seq'::regclass) name | character varying | not null group_id | integer | not null Indexes: "vendors_pkey" PRIMARY KEY, btree (id) Foreign-key constraints: "suppliers_group_id_fkey" FOREIGN KEY (group_id) REFERENCES supplier_groups(id) |
The output shows that the suppliers table has a foreign key constraint which references to the supplier_groups table.
Now, we rename the supplier_groups table to groups as follows:
1 | 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:
1 2 3 4 5 6 7 8 9 10 11 | test-# \d suppliers; Table "public.suppliers" Column | Type | Modifiers ----------+-------------------+------------------------------------------------------ id | integer | not null default nextval('vendors_id_seq'::regclass) name | character varying | not null group_id | integer | not null Indexes: "vendors_pkey" PRIMARY KEY, btree (id) Foreign-key constraints: "suppliers_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(id) |
As you can see, the foreign key constraint was updated and referenced to the groups table instead.
The following statement shows the supplier_data view:
1 2 3 4 5 6 7 8 9 10 11 12 13 | test-# \d+ supplier_data; View "public.supplier_data" Column | Type | Modifiers | Storage | Description ----------------+-------------------+-----------+----------+------------- id | integer | | plain | name | character varying | | extended | supplier_group | character varying | | extended | View definition: SELECT s.id, s.name, g.name AS supplier_group FROM suppliers s JOIN groups g ON g.id = s.group_id; |
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 have learned how to rename a table to the new one by using the PostgreSQL RENAME table clause of the ALTER TABLE statement.