Add A Column To An Existing Table

Extend a table's structure by adding a new column with `ALTER TABLE`.

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

Docker-validated Not currently validation-green

Example 1

Add an email column to an existing users table

The email column is added to the table. Existing rows receive NULL for the new column because no default was specified. Use UPDATE to populate the new column for existing rows.

MySQL MariaDB PostgreSQL SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE users (id INT, name VARCHAR(50));

INSERT INTO
  users (id, name)
VALUES
  (1, 'Alice'),
  (2, 'Bob');

Validated query

SQL

ALTER TABLE users
ADD COLUMN email VARCHAR(100);

SELECT
  id,
  name,
  email
FROM
  users
ORDER BY
  id;

Expected result

id	name	email
1	Alice	NULL
2	Bob	NULL

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE users (id INT, name VARCHAR(50));

INSERT INTO
  users (id, name)
VALUES
  (1, 'Alice'),
  (2, 'Bob');

Validated query

SQL

ALTER TABLE users ADD email VARCHAR(100);

SELECT
  id,
  name,
  email
FROM
  users
ORDER BY
  id;

Expected result

id	name	email
1	Alice	NULL
2	Bob	NULL

SQL Server uses ADD without the COLUMN keyword. The resulting table state is identical across all engines.

Useful when

Where this command helps.

adding a new property to an existing table without losing data
evolving a schema after initial deployment

Explanation

What the command is doing.

ALTER TABLE ... ADD inserts a new column into an existing table. All existing rows immediately receive a NULL value for the new column (or the column's default if one is defined). MySQL, MariaDB, and PostgreSQL use the ADD COLUMN keyword; SQL Server uses ADD without COLUMN.

Add A Column To An Existing Table

Add an email column to an existing users table

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.