PostgreSQL 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:

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

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.

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:

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.

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.

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

Output

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

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.

Output

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

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

Output

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