We can rename the column of a table by utilizing the ALTER statement with the RENAME COLUMN statement. The ALTER statement alters the table and the RENAME COLUMN clause will rename the specified column. This tutorial will teach us the different approaches to renaming the column of a table in Postgres. So let’s learn about it in detail.
How to Rename the Columns of a Table in PostgreSQL?
We can use the Postgres queries to rename the column of a table, and the ALTER TABLE statement with the RENAME COLUMN clause is the appropriate way to do it. The query will be written as:
ALTER TABLE t_name
RENAME COLUMN col_name_prev TO col_name_new;
Syntax Description
- The ALTER TABLE statement will alter the table for the changes.
- The table name, that needs to be changed, is written after the ALTER TABLE command.
- The RENAME COLUMN clause will rename the column from the table whose name is specified above.
- The previous name of the column is specified with the RENAME COLUMN statement.
- After the TO keyword, the new column’s name is written.
Let’s see how can we rename a table column in Postgres with the help of an example.
Example 1: Rename a Column of a Table in PostgreSQL
Let’s consider that we have to rename a column of the “account_details” table. The name of the table is “ph_number”. Let’s try to rename the “ph_number” column to “phone_num”. The query for this can be written as:
ALTER TABLE account_details
RENAME COLUMN ph_number TO phone_num;
By executing this query we will successfully be able to rename the “ph_number” column to “phone_num”. This can be seen in the output.
The above query has altered the table to rename the column ph_number to phone_num.
This thing can furthermore be confirmed from the side panel under the table option. You just need to expand the altered table i.e. “account_details” in this case and then expand the “columns” to see the columns. You will see the renamed column under the columns option. In case you don’t, try refreshing the table.
This is how we can rename a column in Postgres.
Example 2: Rename Multiple Columns of a Table in PostgreSQL
We can rename multiple columns of a table in the same way. Let’s write a query to rename both the columns of the “account_details” in PostgreSQL.
ALTER TABLE account_details
RENAME COLUMN phone_num TO phone_number;
ALTER TABLE account_details
RENAME COLUMN username TO user_name;
The above query will alter the table to rename both columns i.e. “phone_num” to “phone_number” and “username” to “user_name”. The output of the query is:
We can confirm and verify this from the side panel.
So this is how we can rename the columns of a table.
Note: Note that we can not use the ALTER IF EXISTS statement to rename the columns as the ALTER IF EXISTS is not supported to rename the columns by Postgres.
Rename the Columns Manually
We can also rename the column manually using pgAdmin. To do this, go to the table where this column is present. Right-click on the table, this will open a drop-down menu for you. Select the “Properties” option from this menu.
This will open a new property window for you.
Go to the “columns” tab, and you can simply rename the columns from the name field of the column. Hit the “Save” button at the end to save the renamed changes.
We can verify the modified column names by expanding the “columns” option from the side panel.
We can observe that both columns have been renamed in the account_details table.
Conclusion
To rename the columns, we use the ALTER TABLE statement with the aid of the RENAME COLUMN clause. These statements when used together, can rename the columns of the table. The ALTER statement alters the table for some changes. The RENAME COLUMN command renames the column or multiple columns. In this tutorial, we have considered the different methods to rename the column of a table in Postgres.
