Change Column Type

In this section, we are going to discuss how we can change the columns data type with the help of the ALTER TABLE command.

Command of changing the column data type

We will use the ALTER TABLE command to modify the column’s data type.

The basic syntax of changing the column datatype is as follows:

ALTER TABLE table_name  

ALTER COLUMN column_name [SET DATA] TYPE new_data_type; 

In the above syntax, we will use the following parameters:

about:blank

Parameters	Description
Table_name	It is used to define the table’s name, where we want to modify the column data type.
Column_name	It is used to provide the column’s name, where the data type will be modified in the ALTER COLUMN condition.
New_data_type	It is used after the Type keyword, and for the columns reference. It is likely to use either TYPE or SET DATA TYPE.

The Syntax for changing the multiple columns in a single command

We will use the below syntax to modify the data types of several columns in a single command.

ALTER TABLE table_name  

ALTER COLUMN column_name_1 [SET DATA] TYPE new_data_type,  

ALTER COLUMN column_name_2 [SET DATA] TYPE new_data_type; 

As we can see in the above syntax, we can make discrete each ALTER COLUMN conditions with the help of comma (,) to transform the types of various columns at a time.

PostgreSQL offers us to change the old column values to the new one while modifying the column’s data type by adding a USING condition as we can see in the below command:

 ALTER TABLE table_name  

ALTER COLUMN column_name TYPE new_data_type USING expression; 

Note: In PostgreSQL, we can use the USING clause to add the new column values from the old ones.

The PostgreSQL will create old column values to the new one indirectly if we are not using the USING condition. And if the creation is failed, PostgreSQL will raise an issue and ask us to give the USING clause with an expression that is used for alteration.

Examples of PostgreSQL change column type

To show the changes in the column type, we will create a new table called Student_information.

about:blank

CREATE TABLE Student_information(  

Stu_id serial PRIMARY KEY,  

Stu_name TEXT NOT NULL,  

Stu_agr int,  

Stu_address char(30)  

); 

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

After that, we will be inserting some rows into a particular table.

INSERT INTO Student_information (  

Stu_name, Stu_age,Stu_address)  

VALUES('Mike','26','New york' ),  

 ('Emily','23','Boston'),  

 ('john','25','Chicago');

Output

Once we execute the above command, we will get the following message window: the three rows have been inserted into the Student_information table.

We will use the following command to modify the data type of the Stu_name column to Varchar

ALTER TABLE Student_information ALTER COLUMN Stu_name TYPE VARCHAR;  

Output

After executing the above command, we will get the below result:

about:blank

The following command will help us to alter the data types of Stu_age and Stu_address columns from int to Varchar and char to Varchar.

 ALTER TABLE Student_information   

ALTER COLUMN Stu_age TYPE VARCHAR,  

 ALTER COLUMN Stu_address TYPE VARCHAR; 

Output

Once we execute the above command, we will get the following message window: Student_information table has been altered.

about:blank

If we want to check that all the operations we perform above, we will use the Select command:

Select * from Student_information;  

Output

After executing the above command, we will get the below output:

about:blank