DDL

ALTER TABLE ADD COLUMN

Q: How do I add a column to an existing table in SQL?

ALTER TABLE table_name ADD COLUMN column_name data_type; works on every major engine. Optionally add NOT NULL, DEFAULT value, or both. Example: ALTER TABLE users ADD COLUMN phone VARCHAR; adds a nullable phone column to the users table.

Q: Can I add multiple columns at once?

On Postgres, MySQL, and SQL Server: yes, comma-separated ADD COLUMN clauses inside a single ALTER TABLE: ALTER TABLE users ADD COLUMN phone VARCHAR, ADD COLUMN country VARCHAR(2);. SQLite and Oracle require one statement per column.

Q: Can I make ALTER TABLE ADD COLUMN idempotent?

Yes: ALTER TABLE users ADD COLUMN IF NOT EXISTS phone VARCHAR; on Postgres and DuckDB. The statement is then safe to re-run without error. MySQL, SQL Server, and SQLite don't support the IF NOT EXISTS clause; check information_schema.columns first or wrap in a try/catch in migration tooling.

Add a column to an existing table without rebuilding it. The new column appears at the end, exists for every existing row (filled with NULL or the supplied default), and is available to subsequent INSERT and UPDATE statements.

Syntax #

sql

ALTER TABLE table_name
ADD COLUMN column_name data_type [NOT NULL] [DEFAULT value];

Example #

Loading SQL editor...

Common variants #

sql

-- Idempotent in Postgres / DuckDB.
ALTER TABLE users ADD COLUMN IF NOT EXISTS phone VARCHAR;

-- Multiple columns at once (Postgres, MySQL, SQL Server).
ALTER TABLE users
  ADD COLUMN address   VARCHAR,
  ADD COLUMN country   VARCHAR(2),
  ADD COLUMN is_pro    BOOLEAN DEFAULT FALSE;

-- NOT NULL with a default — safe.
ALTER TABLE users ADD COLUMN role VARCHAR NOT NULL DEFAULT 'member';

-- NOT NULL without a default — fails on a non-empty table.
-- ALTER TABLE users ADD COLUMN role VARCHAR NOT NULL; -- ERROR

Dialect notes #

Multiple columns in one statement: Postgres, MySQL, and SQL Server support comma-separated ADD COLUMN clauses. SQLite and Oracle require one statement per column.
COLUMN keyword: Optional in Postgres, DuckDB, MySQL, SQL Server. Required in SQLite. Just type ADD COLUMN everywhere and you're portable.
Position: Most engines append at the end. MySQL allows AFTER column_name or FIRST to control position. Don't rely on column order for application logic.
Default evaluation: Postgres 11+ stores defaults as metadata (instant); older versions and some engines rewrite the table (slow on large tables). For a billion-row table, check your engine's behavior before you run it.

FAQ #

How do I add a column to an existing table in SQL?

ALTER TABLE table_name ADD COLUMN column_name data_type; works on every major engine. Optionally add NOT NULL, DEFAULT value, or both. Example: ALTER TABLE users ADD COLUMN phone VARCHAR; adds a nullable phone column to the users table.

Can I add multiple columns at once?

On Postgres, MySQL, and SQL Server: yes, comma-separated ADD COLUMN clauses inside a single ALTER TABLE: ALTER TABLE users ADD COLUMN phone VARCHAR, ADD COLUMN country VARCHAR(2);. SQLite and Oracle require one statement per column.

Why does ADD COLUMN ... NOT NULL fail on a non-empty table?

The engine has nothing to put in the existing rows. The safe pattern: add the column nullable first, backfill values with an UPDATE, then add the NOT NULL constraint with ALTER TABLE ... ALTER COLUMN ... SET NOT NULL. Or supply a DEFAULT in the original ADD COLUMN so existing rows get a value automatically.

Will adding a column lock my table?

On Postgres 11+, adding a column with no default (or a constant default) is metadata-only and instant: no table rewrite, no long lock. Older versions and some other engines rewrite the entire table, which can take hours and lock writers for the duration. Check your engine's behavior before running on a billion-row table.

How do I add a column with a default value?

ALTER TABLE users ADD COLUMN role VARCHAR NOT NULL DEFAULT 'member'; — every existing row gets the default value, and new inserts that don't specify role will also get 'member'. The default is evaluated at ALTER time for existing rows.

Can I make ALTER TABLE ADD COLUMN idempotent?

Yes: ALTER TABLE users ADD COLUMN IF NOT EXISTS phone VARCHAR; on Postgres and DuckDB. The statement is then safe to re-run without error. MySQL, SQL Server, and SQLite don't support the IF NOT EXISTS clause; check information_schema.columns first or wrap in a try/catch in migration tooling.

Warning

ADD COLUMN ... NOT NULL without a default fails on a non-empty table — the engine has nothing to put in the existing rows. The safe pattern: add the column nullable, backfill with an UPDATE, then add the NOT NULL constraint with ALTER TABLE ... ALTER COLUMN ... SET NOT NULL. Or supply a DEFAULT in the original ADD COLUMN so existing rows get a value.