How to Add a Column to an Existing Table in SQL

Posted by Kyle Hankinson March 10, 2022

---

Adding a column to an existing table is one of the most common schema changes. The basic syntax is similar across databases, but there are important differences in how defaults and constraints are handled.

Basic Syntax

ALTER TABLE table_name ADD column_name data_type;

With a Default Value

-- MySQL
ALTER TABLE users ADD is_active TINYINT(1) NOT NULL DEFAULT 1;

-- PostgreSQL
ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE;

-- SQL Server
ALTER TABLE users ADD is_active BIT NOT NULL DEFAULT 1;

-- Oracle
ALTER TABLE users ADD (is_active NUMBER(1) DEFAULT 1 NOT NULL);

-- SQLite
ALTER TABLE users ADD COLUMN is_active INTEGER NOT NULL DEFAULT 1;

Note: Oracle puts the column definition in parentheses and places DEFAULT before NOT NULL.

Adding NOT NULL Columns to Tables with Data

If the table already has rows, adding a NOT NULL column without a DEFAULT will fail in most databases because the existing rows would have NULL values.

The solution is to always provide a default:

ALTER TABLE users ADD created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

Or add the column as nullable first, backfill data, then add the constraint:

-- Step 1: Add nullable column
ALTER TABLE users ADD created_at TIMESTAMP;

-- Step 2: Backfill
UPDATE users SET created_at = '2020-01-01 00:00:00' WHERE created_at IS NULL;

-- Step 3: Add NOT NULL constraint
ALTER TABLE users ALTER COLUMN created_at SET NOT NULL;  -- PostgreSQL
ALTER TABLE users MODIFY created_at TIMESTAMP NOT NULL;  -- MySQL
ALTER TABLE users ALTER COLUMN created_at TIMESTAMP NOT NULL;  -- SQL Server

Adding Multiple Columns

-- MySQL / PostgreSQL
ALTER TABLE users
    ADD COLUMN first_name VARCHAR(50),
    ADD COLUMN last_name VARCHAR(50);

-- SQL Server
ALTER TABLE users
    ADD first_name VARCHAR(50),
        last_name VARCHAR(50);

-- Oracle
ALTER TABLE users ADD (
    first_name VARCHAR2(50),
    last_name VARCHAR2(50)
);

Column Position (MySQL Only)

MySQL lets you control where the new column appears:

ALTER TABLE users ADD email VARCHAR(255) AFTER username;
ALTER TABLE users ADD id INT FIRST;

Other databases always add columns at the end.

SQLite Limitations

SQLite's ALTER TABLE is limited — you can only:

• Add a column (with restrictions)
• Rename a column (SQLite 3.25+)
• Rename the table

You cannot drop a column (before SQLite 3.35.0), add constraints to existing columns, or change a column's type. The workaround is to recreate the table.

Using SQLPro Studio

In SQLPro Studio, you can add columns visually using the table structure editor. Select a table, open the structure tab, and click "Add Column" — set the name, type, default value, and nullable flag without writing any SQL. SQLPro Studio generates the correct ALTER TABLE statement for your database automatically.

---