How to Add a Column in SQL

What you’ll build or solve

You’ll modify a table by adding one or more new columns.

When this approach works best

Adding a column works best when you:

• Introduce a new feature that requires storing extra data
• Track additional attributes such as status, timestamps, or flags
• Adjust your schema after discovering missing information

For example, you might add a last_login column to users, a discount column to products, or a shipped_at column to orders.

This is a bad idea if you change a production table without testing. Structural updates can affect queries and application code.

Prerequisites

• Access to the database
• Permission to alter tables
• Basic SQL knowledge

Step-by-step instructions

Step 1: Add a column with ALTER TABLE

Use ALTER TABLE ... ADD to introduce a new column.

Basic syntax in MySQL or PostgreSQL

ALTERTABLE users
ADDCOLUMN last_loginTIMESTAMP;

SQL Server (omit COLUMN)

ALTERTABLE users
ADD last_login DATETIME;

The new column is added to all existing rows. Its value is NULL unless you define a default.

You can also include additional options directly in the same command.

Add a default value

ALTERTABLE users
ADDCOLUMN is_activeBOOLEANDEFAULTTRUE;

In SQL Server:

ALTERTABLE users
ADD is_activeBITDEFAULT1;

Add NOT NULL (with a default for existing rows)

ALTERTABLE users
ADDCOLUMNroleVARCHAR(50)NOTNULLDEFAULT'member';

If the table already contains data, combining NOT NULL with a default prevents errors.

Add multiple columns in one statement

MySQL or PostgreSQL:

ALTERTABLE products
ADDCOLUMN weightDECIMAL(10,2),
ADDCOLUMN stockINTDEFAULT0;

SQL Server:

ALTERTABLE products
ADD weightDECIMAL(10,2),
    stockINTDEFAULT0;

This runs the same ADD operation for more than one column at once.

What to look for

• Existing rows receive NULL unless a DEFAULT is defined
• Adding NOT NULL without a DEFAULT fails if rows already exist
• SQL Server does not use the COLUMN keyword
• The column name must not already exist in the table
• Update application code that depends on the table structure

Examples you can copy

Example 1: Add a login timestamp

You want to track when users log in.

ALTERTABLE users
ADDCOLUMN last_loginTIMESTAMP;

Your application can now update last_login whenever a user signs in.

Example 2: Add a required status column

You need every task to have a status.

ALTERTABLE tasks
ADDCOLUMN statusVARCHAR(20)NOTNULLDEFAULT'pending';

Existing rows receive pending automatically.

Example 3: Add pricing support

You introduce a discount feature.

ALTERTABLE products
ADDCOLUMN discountDECIMAL(5,2)DEFAULT0.00;

All current products start with a discount value of 0.00.

Common mistakes and how to fix them

Mistake 1: Adding NOT NULL without a default

What you might do:

ALTERTABLE users
ADDCOLUMN ageINTNOTNULL;

Why it fails: If the table already has rows, the database cannot assign a value to the new column.

Correct approach:

ALTERTABLE users
ADDCOLUMN ageINTNOTNULLDEFAULT0;

Or allow NULL, update the rows, then modify the constraint later.

Mistake 2: Using the wrong syntax for your database

What you might do:

ALTERTABLE users
ADDCOLUMN last_login DATETIME;

Why it fails: SQL Server does not support the COLUMN keyword in this context.

Correct approach:

ALTERTABLE users
ADD last_login DATETIME;

Match the syntax to your database system.

Mistake 3: Forgetting to update insert statements

What you might do: Add a required column and leave old insert queries unchanged.

Why it causes issues: If the column is NOT NULL without a default, inserts may fail.

Correct approach: Update inserts:

INSERTINTO users (name,role)
VALUES ('Alex','member');

Or define a default value during column creation.

Troubleshooting

• If you see “permission denied,” confirm you have ALTER privileges.
• If you see “column already exists,” choose a different name.
• If adding a NOT NULL column fails, check for existing rows and add a default.
• If inserts start failing after the change, review required fields and defaults.

Quick recap

• Use ALTER TABLE ... ADD to add a column
• Define the data type clearly
• Add DEFAULT if existing rows need a value
• Combine NOT NULL with a default for populated tables
• Adjust syntax for your database
• Update dependent application code