PostgreSQL Alter Column Type with Examples

PostgreSQL offers a range of data types. Use ALTER TABLE command to alter column type to a different data type.

The ALTER TABLE command syntax.

ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_data_type;

1. To modify the column type for a target table, substitute table_name with the desired table’s name.
2. The current column name for which you wish to modify the data type should be replaced with column_name.
3. Replace new_data_type with the new desired data type.

Alter Column Type Example (Change Type Integer to VARCHAR)

For example, we have the “item_type” column, which is of the integer data type. In this example, we are converting an INTEGER column to a VARCHAR data type.

ALTER TABLE products 
ALTER COLUMN item_type TYPE VARCHAR(5)

PostgreSQL will do an internal cast to convert existing values to a new type. If implicit casts fail, then PostgreSQL will throw an error. For example, if we have an integer greater than the digit 5 (123456), then conversion to VARCHAR(5) will fail.

To forcefully do the conversion, use the USING clause. column_name::new_data_type is the syntax for using a clause. Below is an example.

ALTER TABLE products 
ALTER COLUMN item_type TYPE VARCHAR(5) USING item_type::VARCHAR(5)

Change Type for Multiple Columns Example

You can change multiple column data types with the single ALTER TABLE command. The new data type must be entered alongside the name of an existing column in multiple lines, separated by commas. Below is an example.

ALTER TABLE products 
ALTER COLUMN item_type TYPE VARCHAR(5),
ALTER COLUMN name TYPE VARCHAR(100);

ALTER Column Type to Not Null

To add a not-null constraint to an existing column, use the ALTER TABLE command with the SET command, as shown below.

ALTER TABLE products 
ALTER COLUMN name SET NOT NULL;

Date to Timestamp

One of the most common scenarios is to change the type of date data type to a timestamp data type. PostgreSQL provides two timestamp data types.

1. timestamp: store date and time without time-zone information
2. timestamp: store date and time with time-zone information.

You can choose any timestamp data type based on your requirements.

Below is an example of converting a date data type to a timestamp data type.

ALTER TABLE products 
ALTER COLUMN action_date TYPE timestamp;

Summary

The ALTER TABLE command is useful for altering column data types. To change the data type forcibly, use the USING clause.

Read More

1. PostgreSQL data types with Examples
2. Insert Multiple Rows in PostgreSQL