SQL ALTER TABLE ADD COLUMN: Syntax and Examples

When a table already exists, you extend it with ALTER TABLE instead of running CREATE TABLE again. That mistake is common in GUI tools: you edit the table script, hit execute, and the engine reports the object already exists.

This page walks through adding one column, several columns at once, defaults, and NOT NULL on tables that already hold rows. The core ALTER TABLE … ADD syntax is the same on SQL Server, MySQL/MariaDB, and PostgreSQL; only a few cases differ and are listed under Where syntax differs.

Tested on: MariaDB 11.4.7; PostgreSQL 17.7; Microsoft SQL Server 2022.

Quick answer: add a column in SQL

ALTER TABLE … ADD appends a new column. Existing rows stay in place; the new column is usually NULL until you update it or define a DEFAULT.

ALTER TABLE table_name
ADD column_name data_type;

Add an email column to employees:

ALTER TABLE employees
ADD email VARCHAR(100);

That statement works unchanged on SQL Server, MySQL/MariaDB, and PostgreSQL. See where syntax differs for the few exceptions.

Setup: sample table

The examples use a two-row employees table. If you are new to table design, see SQL CREATE TABLE first.

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

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

Add one column

Add a nullable email column. No existing data is removed; Alice and Bob simply gain a new field set to NULL.

ALTER TABLE employees
ADD email VARCHAR(100);

On MariaDB, DESCRIBE confirms the column was appended and allows NULL:

Field   Type          Null  Key  Default  Extra
id      int(11)       NO    PRI  NULL
name    varchar(100)  YES        NULL
email   varchar(100)  YES        NULL

The Null column shows YES for email, so both existing rows store NULL there until you run an UPDATE.

Add multiple columns in one statement

List each new column after a single ALTER TABLE. On MariaDB and SQL Server, comma-separate the definitions under one ADD:

ALTER TABLE employees
ADD dept VARCHAR(50),
    notes VARCHAR(50);

After running that on MariaDB:

Field   Type          Null  Key  Default  Extra
id      int(11)       NO    PRI  NULL
name    varchar(100)  YES        NULL
email   varchar(100)  YES        NULL
dept    varchar(50)   YES        NULL
notes   varchar(50)   YES        NULL

Both new columns appear at the end with Null set to YES.

PostgreSQL equivalent:

ALTER TABLE employees
ADD COLUMN dept VARCHAR(50),
ADD COLUMN notes VARCHAR(50);

Add a column with a DEFAULT value

A DEFAULT back-fills existing rows and applies on future INSERTs when the column is omitted. For more patterns, see add a column with a default value.

Add salary defaulting to 0.00:

ALTER TABLE employees
ADD salary DECIMAL(10, 2) DEFAULT 0.00;

id  name   email  salary
1   Alice  NULL   0.00
2   Bob    NULL   0.00

0.00 was written for both rows even though they were inserted before salary existed. The same DEFAULT pattern works on SQL Server, MySQL/MariaDB, and PostgreSQL.

Add a NOT NULL column when the table already has rows

A plain NOT NULL column cannot be added to a non-empty table: every existing row would need a value immediately. See SQL NOT NULL constraint for the wider rules around nullability.

This fails on SQL Server without a DEFAULT:

Msg 4901, Level 16, State 1
ALTER TABLE only allows columns to be added that can contain nulls, or have a
DEFAULT definition specified … Column 'bad_col' cannot be added to non-empty
table 'employees' because it does not satisfy these conditions.

PostgreSQL reports:

ERROR:  column "bad_col" of relation "employees" contains null values

Pair NOT NULL with DEFAULT so existing rows receive a value:

ALTER TABLE employees
ADD phone VARCHAR(20) NOT NULL DEFAULT 'Unknown';

MariaDB after the change:

Field   Type          Null  Key  Default  Extra
phone   varchar(20)   NO         Unknown

id  name   phone
1   Alice  Unknown
2   Bob    Unknown

phone is NO under Null, and both rows show Unknown without a separate UPDATE.

Verify the new columns

Pick the catalog command your client provides and confirm names, types, and nullability.

On MariaDB:

DESCRIBE employees;

On SQL Server:

SELECT name, is_nullable
FROM sys.columns
WHERE object_id = OBJECT_ID('employees')
ORDER BY column_id;

name     is_nullable
id       0
name     1
phone    0

is_nullable 0 means the column is NOT NULL; 1 means nulls are allowed.

In psql, run \d employees to list PostgreSQL column definitions.

Where syntax differs by engine

The examples above use syntax that runs on all three engines. The differences below are the ones you are most likely to hit in practice.

SQL Server

Use NVARCHAR for Unicode text columns. To add a column only when it is missing:

IF COL_LENGTH('employees', 'extension') IS NULL
  ALTER TABLE employees ADD extension NVARCHAR(10) NULL;

For a date default on new columns, SQL Server uses GETDATE():

ALTER TABLE employees
ADD hire_date DATE NOT NULL DEFAULT CAST(GETDATE() AS DATE);

Official reference: Add columns to a table (Microsoft Learn).

MySQL / MariaDB

The optional COLUMN keyword does not change behavior:

ALTER TABLE employees ADD COLUMN phone VARCHAR(20);

Only MySQL and MariaDB let you control column position when adding:

ALTER TABLE employees
ADD COLUMN ext VARCHAR(10) AFTER name;

Field   Type
id      int(11)
name    varchar(100)
ext     varchar(10)
phone   varchar(20)

ext sits between name and phone because of AFTER name. SQL Server and PostgreSQL always append new columns at the end.

For a date default:

ALTER TABLE employees
ADD hire_date DATE NOT NULL DEFAULT (CURRENT_DATE);

PostgreSQL

When adding several columns in one statement, repeat ADD COLUMN before each name (shown earlier in Add multiple columns).

When you need NOT NULL but must backfill values first:

ALTER TABLE employees ADD status VARCHAR(20);
UPDATE employees SET status = 'active' WHERE status IS NULL;
ALTER TABLE employees ALTER COLUMN status SET NOT NULL;

id | name  | status
----+-------+--------
  1 | Alice | active
  2 | Bob   | active

Both rows were updated before SET NOT NULL ran, so the constraint applies cleanly.

For a date default:

ALTER TABLE employees
ADD hire_date DATE NOT NULL DEFAULT CURRENT_DATE;

Common mistakes

To drop a table entirely, see how to delete a table in SQL.

Summary

ALTER TABLE … ADD extends a live table without recreating it. The core one-line form is the same on SQL Server, MySQL/MariaDB, and PostgreSQL. Differences appear for PostgreSQL multi-column ADD COLUMN, MySQL column position, date default functions, and optional SQL Server guards — see Where syntax differs.

References

• SQL ALTER TABLE (W3Schools) — rename, modify, and drop column overview
• Add columns to a table (Microsoft Learn) — SQL Server and Azure SQL