In this tutorial, we will learn how can we rename the table using the RENAME command. We have to use the RENAME keyword with the ALTER TABLE statement to make it work. So, let’s see how it is done!
PostgreSQL RENAME Table Statement
First of all, the RENAME keyword cannot be used standalone to rename any table. It is used with the ALTER TABLE statement which is used to modify the table structure.
In many database systems, RENAME keyword functionality is given out of the box to rename the table without using the ALTER TABLE statement.
However, it is very simple to use the RENAME command. Let’s see its syntax.
PostgreSQL RENAME Table Syntax
Following is the syntax for renaming the table:
ALTER TABLE [ IF EXISTS ] tableName
RENAME TO newTableName;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, the IF EXISTS clause will give you the notice instead of an error if the table that you are trying to change the name doesn’t exist.
Another thing worth noting is, the RENAME clause is also used to rename the column as well. The only difference between these two uses is, you need to mention the column name when renaming the column. If you don’t mention the column name, PostgreSQL will consider you are trying to update the table name.
If you want to rename multiple tables, you can’t do that with a single query. You have to execute the same query multiple times.
Now let’s see the example of renaming the table.
PostgreSQL RENAME Table Examples
Let’s create the table first before renaming it.
DROP TABLE IF EXISTS country;
CREATE TABLE country(
ID SERIAL PRIMARY KEY,
Name VARCHAR(50),
Code VARCHAR(3) UNIQUE,
Population BIGINT
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Check: How to Create a Table in PostgreSQL?
Let’s now rename the table using the ALTER TABLE statement.
Rename Independent Table
ALTER TABLE IF EXISTS country
RENAME TO countryDetail;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see, our table is successfully renamed.
Rename Parent Table
But what will happen if there is another table referenced to the recently created table (countrydetail) and we try to rename the parent table??
Let’s try.
But first, let’s create another table that is referenced to the ‘countrydetail’ table.
DROP TABLE IF EXISTS state;
CREATE TABLE state(
ID SERIAL PRIMARY KEY,
Name VARCHAR(50),
CountryCode VARCHAR(3),
FOREIGN KEY(CountryCode) REFERENCES countryDetail (code)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Perfect! You can check the relationship details of the table using the \d command.
Now that our child table is created and referenced to the ‘countrydetail’ table, we can try renaming the parent table.
Let’s try renaming the ‘countryDetail’ table again.
ALTER TABLE IF EXISTS countryDetail
RENAME TO countryInformation;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see, we didn’t get any error and the table has been renamed successfully. If you check the output above, the relationship details of the ‘state’ table have been changed automatically. You don’t have to write anything explicitly here.
Rename Non-Existing Table
What if you try to rename the table that doesn’t exists? Let’s see.
ALTER TABLE notATable
RENAME TO yesATable;
ALTER TABLE IF EXISTS notATable
RENAME TO yesATable;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see here, we get an error.
But, to get rid of this error, PostgreSQL provides us with the IF EXISTS clause to use with the ALTER TABLE statement as shown in the syntax.
The second query returns the notice instead of the error because we have used the IF EXISTS clause.
Conclusion
In this tutorial, we have learned to rename the table using the RENAME keyword with the ALTER TABLE statement. We have learned some other things related to renaming the table as well which you might have not known.