Javatpoint Logo
Javatpoint Logo

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:

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.

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.

PostgreSQL Change Column Type

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.

PostgreSQL Change Column Type

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:

PostgreSQL Change Column Type

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.

PostgreSQL Change Column Type

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:

PostgreSQL Change Column Type




Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA