Modify Column Type in PostgreSQL

Use ALTER TABLE ALTER COLUMN statement to change data type of column. Here after ALTER TABLE, specify name of table to which you want to modify column, then after ALTER COLUMN, specify name of column to which you want to change data type. After TYPE keyword, specify the new data type of column.

Syntax: 

ALTER TABLE [schema_name.]table_name 
ALTER COLUMN <column_name> [SET DATA] TYPE <new_data_type>,
ALTER COLUMN <column_name> [SET DATA] TYPE <new_data_type>
...;

        

PostgreSQL allows changing data types of multiple columns by using one ALTER TABLE statement with multiple ALTER COLUMN clauses. It requires to add comma , after each ALTER COLUMN clause.

Consider that you already have the following employee table.

Now to change datatype of email from VARCHAR to TEXT.

Example: Modify Column Type

ALTER TABLE employee
ALTER COLUMN email TYPE TEXT;

PostgreSQL does implicit auto-casting of data from old data type to new data type when you do ALTER COLUM. In some cases, like converting VARCHAR to INT, casting fails and PostgreSQL will give error that column cannot be casted automatically and will ask you to specify manual casting by specifying the USING clause.

Syntax: 

ALTER TABLE <table_name>
ALTER COLUMN <column_name> TYPE <new_data_type> USING <expression>;

The following changes the data type of the salary column from integer to INT.

Example: Modify Column Datatype

ALTER TABLE employee 
ALTER COLUMN salary TYPE integer USING salary::INT;

Alter Column Type using pgAdmin

You can change name, type, and NOT NULL constraint for one or more columns in pgAdmin by right clicking on the table name and select 'Properties'. In the popup, go to 'Columns' tab wherein you can edit the columns name, type, length, and set/unset NOT NULL constraint by clicking on the edit icon against the column, as shown below.

Alternatively, you can also change the type of a column in pgAdmin by right clicking on the column and select 'Properties' in the context menu. This will open a popup where you can edit the column properties.

Go to Definition tab and edit the type by selecting appropriate type from the Data type dropdown, as shown below.

Click on the Save button to save the changes.