How to alter column type in SQL?

Syntax

To change a column type from an existing table, here is the generic syntax (MySQL, Oracle):

ALTER TABLE table_name
MODIFY [COLUMN] column_name new_data_type;

In SQL Server, PostgreSQL, the syntax is a little different:

ALTER TABLE table_name
ALTER COLUMN column_name new_data_type;

Example

We have table employees:

CREATE TABLE employees (
  emp_no int(11) NOT NULL,
  birth_date date NOT NULL,
  first_name varchar(14) NOT NULL,
  last_name varchar(16) NOT NULL,
  gender enum('M','F') NOT NULL,
  hire_date date NOT NULL,
  email text,
  PRIMARY KEY (emp_no)

We need to change datatype of the column email from text to varchar(50):

ALTER TABLE employees
MODIFY email varchar(50);

---

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

Need a quick edit on the go? Download for iOS