Change a Column's Data Type With ALTER TABLE

Modify the data type, default value, or nullability of an existing column without recreating the table.

4/4 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Widen the description column from VARCHAR(50) to VARCHAR(200)

The column is widened from VARCHAR(50) to VARCHAR(200). Existing rows (id 1 and 2) are unaffected — widening never truncates data. A new row with an 88-character description is then inserted successfully. On PostgreSQL, TYPE VARCHAR(200) is equivalent to TYPE CHARACTER VARYING(200); a USING clause is not needed here because the new type is a superset of the old one.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE products (id INT, description VARCHAR(50));

INSERT INTO
  products
VALUES
  (1, 'Short description'),
  (2, 'Another item');

Validated query

SQL

ALTER TABLE products MODIFY COLUMN description VARCHAR(200);

INSERT INTO
  products
VALUES
  (
    3,
    'This is a much longer description that would not have fit in fifty characters previously'
  );

SELECT
  id,
  description
FROM
  products
ORDER BY
  id;

Expected result

id	description
1	Short description
2	Another item
3	This is a much longer description that would not have fit in fifty characters previously

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE products (id INT, description VARCHAR(50));

INSERT INTO
  products
VALUES
  (1, 'Short description'),
  (2, 'Another item');

Validated query

SQL

ALTER TABLE products
ALTER COLUMN description VARCHAR(200);

INSERT INTO
  products
VALUES
  (
    3,
    'This is a much longer description that would not have fit in fifty characters previously'
  );

SELECT
  id,
  description
FROM
  products
ORDER BY
  id;

Expected result

id	description
1	Short description
2	Another item
3	This is a much longer description that would not have fit in fifty characters previously

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE products (id INT, description VARCHAR(50));

INSERT INTO
  products
VALUES
  (1, 'Short description'),
  (2, 'Another item');

Validated query

SQL

ALTER TABLE products
ALTER COLUMN description TYPE VARCHAR(200);

INSERT INTO
  products
VALUES
  (
    3,
    'This is a much longer description that would not have fit in fifty characters previously'
  );

SELECT
  id,
  description
FROM
  products
ORDER BY
  id;

Expected result

id	description
1	Short description
2	Another item
3	This is a much longer description that would not have fit in fifty characters previously

MySQL/MariaDB use MODIFY COLUMN. SQL Server uses ALTER COLUMN. PostgreSQL uses ALTER COLUMN … TYPE. Results are identical across supported engines.

Useful when

Where this command helps.

widening a VARCHAR column that has hit its length limit
promoting an INT column to BIGINT before inserting values larger than 2,147,483,647

Explanation

What the command is doing.

MySQL and MariaDB use ALTER TABLE … MODIFY COLUMN col new_type to change a column definition, or ALTER TABLE … CHANGE COLUMN old_name new_name new_type to rename and retype in one step. PostgreSQL uses ALTER TABLE … ALTER COLUMN col TYPE new_type, with an optional USING expression for the conversion. SQL Server uses ALTER TABLE … ALTER COLUMN col new_type. SQLite does not support changing a column's type — the only escape is to recreate the table with the new schema and copy the data. Any type change that would truncate or lose data is blocked by the engine unless explicit casting is provided.

Change a Column's Data Type With ALTER TABLE

Widen the description column from VARCHAR(50) to VARCHAR(200)

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.