In PostgreSQL, the “ALTER TABLE” and “ALTER COLUMN” commands, along with the TYPE Keyword, are used to change/modify the data type of a column. For example, integer to character, text to varchar, and so on. In PostgreSQL, we can change the data type of one or more than one column using the “ALTER TABLE” and “ALTER COLUMN” commands.

This blog will present a step-by-step guide on changing the column’s data type. So, without any further delay, let’s start.

How to Change/Update the Column’s Data Type in Postgres?

The below-given syntax will assist you in changing the data type of any particular column:

ALTER TABLE tab_name
ALTER COLUMN col_name TYPE new_data_type;

Let’s analyze the above-given syntax step-by-step:

- tab_name represents a table whose column will be altered.

- col_name represents the column to be altered.

- TYPE is a keyword.

- new_data_type represents the altered/modified data type of the selected column.

Example: How to Change/Modify the Column’s Type From int to text?

You have to follow the below-listed procedure to change the column’s data type:

Step 1: Access a Database

Firstly, open SQL SHELL and type the “\c” command followed by the database name to make a connection with the selected database:

\c example;

Step 2: Available Tables

Once you are connected to the targeted database, type the “\dt” command to see the list of available tables in that database:

\dt;

Step 3: Describe the Table

Select a table and run the following command to see the structure of the selected table:

\d team_info;

Step 4: Change Column Type

Execute the below-given command to change the data type of the “team_rating” column from “integer” to “character”:

ALTER TABLE team_info
ALTER COLUMN team_rating TYPE VARCHAR(30);

Step 5: Verify the Column Type

Let’s run the “\d” command followed by table name to see the changes made to the selected table:

\d team_info;

The output authenticates that the data type of the “team_rating” column has been updated to the character type.

How to Change/Modify the Type of Several Columns With a Single Command?

Follow the comma-separated syntax to change the data type of more than one column using a single query:

ALTER TABLE tab_name
ALTER COLUMN col_1 TYPE new_data_type,
ALTER COLUMN col_2 TYPE new_data_type;

Example: How to update data types of several columns using a single command in PostgreSQL?

From the snippet shown in step 4 of the previous example, we can observe that the “team_rating” and “team_lead” columns have a “character” data type. Suppose we have to change both columns' data types from character to text. To do so, we have to follow the below-given process:

Step 1: Change the Column Type

We can change the type of selected columns by executing the following query/command:

ALTER TABLE team_info
ALTER COLUMN team_rating TYPE TEXT,
ALTER COLUMN team_lead TYPE TEXT;

Step 2: Verify the Column Type

Let’s execute the below-given command to check and verify the data type of the selected columns:

\d team_info;

The snippet given above proved that the data type of the “team_rating” and “team_lead” columns have been successfully updated to the “text” data type.

Conclusion

In PostgreSQL, the “ALTER TABLE” and “ALTER COLUMN” commands are used along with the TYPE Keyword to change/modify the data type of a column. These commands allow us to change/modify the type of an individual or multiple columns simultaneously. This post explained how to change the column type in PostgreSQL with the help of suitable examples.