Rename Column

In this section, we are going to understand how the PostgreSQL Rename column condition works in the Alter table command for renaming one and various columns of a table.

PostgreSQL Rename COLUMN command

We use the ALTER TABLE command with the Rename Column condition to rename a column of a table.

Syntax

The syntax of the Alter table rename column command is given below:

  ALTER TABLE table_name   

RENAME COLUMN column_name TO new_column_name;  

In the above syntax, we have the following parameters:

Parameter	Description
Alter table	It is a clause, which is used to modify the definition of a table.
Table_name	It is used to describe the table name where we want to rename a column after the ALTER TABLE clause.
Column_name	It is used to specify the column name after the RENAME COLUMN clause.
New_cloumn _name	It is used to specify the new column name after the TO keyword clause.

In the above syntax, the COLUMN keyword is optional, so we can ignore it as we can see in the below command:

 ALTER TABLE table_name   

RENAME column_name TO new_column_name; 

Note:

• PostgreSQL will raise an error if we try to rename a non-existing column.
• For the Rename condition, PostgreSQL does not offer the IF EXISTS option.

We need to execute the ALTER TABLE RENAME COLUMN command several times to rename multiple columns, and we can rename one column at a time as we can see in the below syntax:

ALTER TABLE table_name  

RENAME column_name_1 TO new_column_name_1;

OR

ALTER TABLE table_name  

RENAME column_name_2 TO new_column_name_2;

If we rename a column which references by additional database objects like foreign key constraints, views, Stored procedures, and triggers and PostgreSQL will also modify all of its dependent objects.

Examples of PostgreSQL RENAME COLUMN

Let us see some examples for our better understanding of where we are going to use the ALTER TABLE RENAME COLUMN.

Firstly, we will create two new tables as Students and Student_groups.

 CREATE TABLE Students (  

St_id serial PRIMARY KEY,  

St_name VARCHAR NOT NULL,  

St_email VARCHAR,  

St_phone_no VARCHAR NOT NULL,  

 St_group_id INT,  

FOREIGN KEY (st_group_id) REFERENCES Student_groups (Stu_id)  

); 

Output

After executing the above command, we get the below message window, which says that the Students table has been created.

 CREATE TABLE Student_groups (  

    Stu_id serial PRIMARY KEY,  

    Stu_name VARCHAR NOT NULL  

); 

Output

After executing the above command, we get the below message window, which says that the Student_groups table has been created.

After that, we will create a student_data view, which depends on the above Students and Student_groups tables

CREATE VIEW Student_data   

AS SELECT  

 s.St_id,  

 s.St_name,  

 g.Stu_name student_group  

FROM  

Students s  

INNER JOIN Student_groups g ON g.Stu_id = s.St_group_id; 

Output

After executing the above command, we get the below message window, which says that the Student_data view has been created.

Example of renaming one column using RENAME COLUMN

To rename the St_email column to the email of the Students table, we will use the below statement for ALTER TABLE RENAME COLUMN command:

 ALTER TABLE Students   

RENAME COLUMN St_email TO email; 

Output

After executing the above command, we get the below message window where the Students table has been altered.

Example of renaming the column which has dependent objects Using RENAME COLUMN

In this example, we will use the ALTER TABLE RENAME COLUMN command to rename the Stu_name column to name of the Student_groups table. And the Stu_name column is used in the Student_data view.

 ALTER TABLE Student_groups   

RENAME COLUMN Stu_name TO name; 

Output

After executing the above command, we get the below message window which says that the student_groups table has been altered.

Now, we can verify whether the column name has been modified or not by using the command given in the below screenshot:

As we can see in the view definition, the Stu_name column has been changed to name.

Examples of renaming the multiple columns using multiple RENAME COLUMN

These statements rename two columns St_name and St_phone_no of the Students table to Student_name and Student_phone_no individually:

ALTER TABLE Students   

RENAME COLUMN St_name TO Student_name; 

 ALTER TABLE Students  

RENAME COLUMN St_phone_no TO Student_phone_no; 

Output

After executing the above command, we get the below message window which says that the Students table has been altered.

If we want to check that above operations are working fine or not, we will use the SELECT command below:

Select * from Students;  

Output

We will get the below output, once we execute the above command: