The “SET DATA TYPE'' or “TYPE” keyword is used with the collaboration of ALTER TABLE and ALTER COLUMN commands to alter/change the column type in PostgreSQL. Multiple ALTER COLUMN commands will be used along with the ALTER TABLE command to alter the type of multiple columns in a single statement.

This write-up will present a comprehensive guide on how to alter the column type in PostgreSQL. So, let’s begin.

How to Alter Column Type in PostgreSQL?

Here is a simple syntax for altering a single column type in PostgreSQL:

ALTER TABLE tab_name
ALTER COLUMN col_name [SET DATA] TYPE modified_data_type;

- Specify the table name to be altered after the ALTER TABLE command.

- Specify the column name to be altered after the ALTER COLUMN command. Next, specify either the “SET DATA TYPE” or “TYPE” keyword followed by the modified data type.

Example: How to Alter the Column Type From TEXT to VARCHAR in PostgreSQL?

We have created a bike_details table in our database. Let’s run the SELECT query to fetch its details:

SELECT * FROM bike_details;

Suppose we have to alter the type of bike_number column from text to varchar. To do so, we will execute the below-given statement:

ALTER TABLE bike_details
ALTER COLUMN bike_number TYPE VARCHAR;

The output shows that the bike_details table has been altered successfully.

Let’s run the SELECT command to see the updated table:

SELECT * from bike_details;

The output shows that the column type has been altered successfully.

How to Alter Multiple Column Type in PostgreSQL?

You have to use several ALTER COLUMN commands to alter the data type of multiple columns. Following will be the syntax for multiple ALTER COLUMN commands:

ALTER TABLE tab_name
ALTER COLUMN col_name_1 [SET DATA] TYPE modified_data_type,
ALTER COLUMN col_name_2 [SET DATA] TYPE modified_data_type,
...
ALTER COLUMN col_name_N [SET DATA] TYPE modified_data_type;

Example #1: How to Alter the Data Type of Multiple Columns?

Suppose we have to alter the data type of the bike_model column from text to varchar and bike_number column from varchar to text. We will run the below-given query to alter the data type of both columns in a single statement:

ALTER TABLE bike_details
ALTER COLUMN bike_model SET DATA TYPE VARCHAR,
ALTER COLUMN bike_number SET DATA TYPE TEXT;

The output shows that the query returned successfully, and the bike_details table has been updated successfully.

Let’s run the SELECT statement to see the altered type of the selected columns:

SELECT * FROM article_details;

The output verified that the data type of the targeted columns had been altered successfully.

Example #2: How to Alter Column Type From VARCHAR to INT in PostgreSQL?

Let’s execute the ALTER TABLE command to modify the type of bike_model column from VARCHAR to INT:

ALTER TABLE bike_details
ALTER COLUMN bike_model SET DATA TYPE INT;

The output shows that Postgres threw an error when we tried to cast the VARCHAR to INTEGER. This is because Postgres doesn’t allow the implicit type casting from VARCHAR/TEXT to INTEGER data type. In PostgreSQL, the USING clause is used to cast the VARCHAR/TEXT data type to INTEGER data type.

Let’s execute the below command to alter the type of bike_model column from VARCHAR to INTEGER:

ALTER TABLE bike_details
ALTER COLUMN bike_model SET DATA TYPE INT
USING bike_model::INTEGER;

The output shows that the bike_details table has been altered. Let’s run the SELECT query to see the updated table:

The output proves that the data type of bike_model has been altered from VARCHAR to INTEGER.

Conclusion

To alter the column’s type in PostgreSQL, use the “SET DATA TYPE” or “TYPE” keyword with the ALTER TABLE and ALTER COLUMN commands. Multiple ALTER COLUMN commands will be used along with the ALTER TABLE command to alter the type of multiple columns in a single statement. This write-up went through some examples to explain how to alter column type in PostgreSQL.