PostgreSQL

PostgreSQL ALTER COLUMN

Change the data type or maximum size of a column in an existing PostgreSQL table.

The ALTER TABLE statement

To change the data type or size of a table column, use the ALTER TABLE statement. It can also add, delete, or modify columns and add or drop constraints on an existing table.

ALTER COLUMN

We will change the data type of the year column in the products table from INT to VARCHAR(4). Use ALTER COLUMN, followed by the TYPE keyword and the new data type:

ALTER TABLE products
ALTER COLUMN year TYPE VARCHAR(4);

PostgreSQL reports:

ALTER TABLE

This confirms that the column definition was changed successfully.

Important: Existing values must be convertible to the new data type. Numbers can usually be converted to text, but text cannot always be converted to numbers. Always test a type change when the table already contains data.

Change the maximum allowed characters

You can use the same syntax to change the maximum length of the color column from VARCHAR(255) to VARCHAR(30):

ALTER TABLE products
ALTER COLUMN color TYPE VARCHAR(30);

PostgreSQL reports:

ALTER TABLE

The color column now accepts text with up to 30 characters. Existing values longer than the new limit must be handled before reducing the column size.

Verify the change

SELECT year, color
FROM products;