Modify Column Type in PostgreSQL
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.
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
Now to change datatype of email from
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
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
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
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.