Summary: this tutorial shows you step by step how to change the data type of a column by using the ALTER TABLE statement.
PostgreSQL change column type statement
To change the data type of a column, you use the ALTER TABLE statement as follows:
1 2 3 | ALTER TABLE table_name ALTER COLUMN column_name [SET DATA] TYPE new_data_type; |
Let’s examine the statement in a greater detail:
- First, specify the name of the table to which the column you want to change belong in the
ALTER TABLEclause. - Second, give the name of column whose data type will be changed in the
ALTER COLUMNclause. - Third, provide the new data type for the column after the
TYPEkeyword. It is possible to use eitherSET DATA TYPEorTYPE.
To change the data types of multiple columns in a single statement, you use the following syntax:
1 2 3 4 | ALTER TABLE table_name ALTER COLUMN column_name_1 [SET DATA] TYPE new_data_type, ALTER COLUMN column_name_2 [SET DATA] TYPE new_data_type, ...; |
In this syntax, you separate each ALTER COLUMN clause by a comma (,) in order to change the types of multiple columns at a time.
PostgreSQL allows you to convert old column values to the new one while changing the data type of a column by adding a USING clause as follows:
1 2 | ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type USING expression; |
The USING clause allows you to compute the new column values from the old ones.
If you omit the USING clause, PostgreSQL will cast old column values to the new one implicitly. In case the casting is failed, PostgreSQL will issue an error and ask you to provide the USING clause with an expression for conversion.
An expression could be as simple as column_name::new_data_type such as price::numeric or as complex as a custom function.
PostgreSQL change column type examples
Let’s create a new table named assets and insert some rows into the table for the demonstration.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | CREATE TABLE assets ( id serial PRIMARY KEY, name TEXT NOT NULL, asset_no VARCHAR NOT NULL, description TEXT, LOCATION TEXT, acquired_date DATE NOT NULL ); INSERT INTO assets ( NAME, asset_no, location, acquired_date ) VALUES ( 'Server', '10001', 'Server room', '2017-01-01' ), ( 'UPS', '10002', 'Server room', '2 |
To change the data type of the name column to VARCHAR, you use the following statement:
1 | ALTER TABLE assets ALTER COLUMN name TYPE VARCHAR; |
The following statement changes the data types of description and location columns from TEXT to VARCHAR:
1 2 3 | ALTER TABLE assets ALTER COLUMN location TYPE VARCHAR, ALTER COLUMN description TYPE VARCHAR; |
To change the data type of the asset_no column to integer, you use the statement below:
1 | ALTER TABLE assets ALTER COLUMN asset_no TYPE INT; |
PostgreSQL issued an error and a very useful hint to ask you to specify the USING clause:
1 2 | ERROR: column "asset_no" cannot be cast automatically to type integer HINT: You might need to specify "USING asset_no::integer". |
Let’s add the USING clause as recommended:
1 2 | ALTER TABLE assets ALTER COLUMN asset_no TYPE INT USING asset_no::integer; |
It worked.
In this tutorial, you have learned how to change the type of a column using the ALTER TABLE statement.