How to Change/Modify Column Type in PostgreSQL

We can manipulate table data by making use of an ALTER statement. This manipulation can be; adding a column to the table, deleting a column, renaming the table or a table column, etc. Sometimes we need to change/modify the data type of a column of an existing Postgres table. The ALTER statement can serve this purpose. 

This tutorial will teach us to modify and change the data type of a column in PostgreSQL.

How to Change/Modify Column Type in PostgreSQL?

We can modify the data type of any specific column of a table. A Postgres query can be utilized to serve this purpose. The basic syntax for the query to modify the column type can be written as

ALTER TABLE t_name
ALTER COLUMN c_name [SET DATA] TYPE new_data_type;

Syntax Description

  • The ALTER TABLE clause alters the table.
  • The name of the table we want to alter is specified after the ALTER TABLE statement.
  • The ALTER COLUMN command is used to alter the column’s data type.
  • The name of the column whose data type is to be altered is written after the ALTER COLUMN command. This command is followed by the “SET DATA” or “TYPE” keyword.
  • The  “SET DATA” or “TYPE” keyword is used to set the data type to a new one.
  • The data type that we want the column to be modified into is to be written after the  “SET DATA” or “TYPE” keywords.

Let’s learn how we can change the data type of any column in PostgreSQL.

Example 1: Change/Modify Column Type in PostgreSQL

We can change/modify the data type of a Postgres column using an ALTER command. Let’s alter the data type of the column “user_name” from the table “account_details”. Follow the steps below:

Step 1: Check the Current Data Type of the Column

We will execute the SELECT statement to see what the table “account_details” looks like and what is the data type of the “user_name” column before changing:

SELECT * FROM account_details

The SELECT statement will return the selected table.

Change/Modify Column Type in PostgreSQL 1

We can observe that the column “user_name” has the data type TEXT.

Step 2: Modify/Change the Data Type of the Column

 Now we will try to modify the data type into “VARCHAR”. To perform this task, we will execute the below-given query:

ALTER TABLE account_details
ALTER COLUMN user_name TYPE VARCHAR;

This query will change the data type of the “user_name” column into “VARCHAR”. The output of the above query is

Change/Modify Column Type in PostgreSQL 2

Step 2: Verify the New Data Type of the Column

To verify the data type of the column “user_name” we will again execute the SELECT query and see if the data type is modified or not.

Change/Modify Column Type in PostgreSQL 3

In this way, we can modify\change the data type of a column.

Example 2: Modifying Data Type of Multiple Columns

We can similarly modify the data type of many columns at the same time. If we change the data type of both the columns, from TEXT to VARCHAR, of the table “account_details”, we can write the following query:

ALTER TABLE account_details
ALTER COLUMN user_name TYPE VARCHAR,
ALTER COLUMN phone_number TYPE VARCHAR
Change/Modify Column Type in PostgreSQL 5

The query has modified the data type of both columns which can be seen by executing the SELECT query:

Change/Modify Column Type in PostgreSQL 6

We can see that the data type of both columns is changed from TEXT to VARCHAR.

Example 3: Modifying Data Type From VARCHAR to INT

Let’s see what happens if we try to change the VARCHAR data type to INT. For this, we will be modifying the data type of the “phone_number” column to INT. For this, we will write the following query:

ALTER TABLE account_details
ALTER COLUMN phone_number TYPE INT

The execution of the query results in an error.

Change/Modify Column Type in PostgreSQL 7

This error is raised because Postgres does not support the implicit type casting from TEXT/VARCHAR data type to INT. If we want to typecast the TEXT/VARCHAR data type to INT without encountering this issue, we will have to add a USING clause in our code, which is used here to cast the TEXT/VARCHAR data type to INT data type. We will write the following query in this regard:

ALTER TABLE account_details
ALTER COLUMN phone_number TYPE INT
USING phone_number::INTEGER;

Now the query will work fine as it will alter the table for the change in the table’s column data type as given below:

Change/Modify Column Type in PostgreSQL 8

We can verify if the data type has changed or not by executing the SELECT statement.

Change/Modify Column Type in PostgreSQL 9

We can see that the data type of the “phone_number” column has been changed from VARCHAR to INT.

In this way, we can change/modify the data type of the column/columns.

Conclusion

We can change/modify the data type of a column or multiple columns by making use of the ALTER statement. The table that needs to be altered needs to be specified in the query. The column whose data type is to be altered is specified after the ALTER COLUMN statement and we have to specify the new data type after the TYPE or SET DATA clause. One thing that is to be noticed is that we can not implicitly typecast the TEXT/VARCHAR data type into the INT data type, to do this we will need a USING clause. In this blog, we have learned to change/modify the data type of columns in Postgres in detail.