SQL - Modify Column Data Type and Size

The ALTER command is a DDL command to modify the structure of existing tables in the database by adding, modifying, renaming, or dropping columns and constraints.

Different databases support different ALTER TABLE syntax to modify the column data type and size.

The following ALTER TABLE statement modifies the size of the Address column of the Employee table in the SQL Server database.

ALTER TABLE Employee ALTER COLUMN FirstName VARCHAR(50);

The following will change the size in the Oracle database.

ALTER TABLE Employee MODIFY (FirstName VARCHAR2(50));

The following will change the size in the PostgreSQL database.

ALTER TABLE Employee 
ALTER COLUMN FirstName TYPE VARCHAR(50);

Change Column Datatype

The following statement will change the data type of PinCode column from varchar to integer in the SQL Server database.

ALTER TABLE Employee 
ALTER COLUMN PinCode integer;

The following statement will change column data type in the Oracle database.

ALTER TABLE Employee MODIFY (PinCode number);

The following statement will change column data type in the PostgreSQL database.

ALTER TABLE Employee 
ALTER COLUMN PinCode TYPE INT
USING PinCode::INTEGER;