How to Modify a Column in SQL

What you’ll build or solve

You’ll update the definition of an existing column in a table.

When this approach works best

Modifying a column works best when you:

• Increase the length of a text field
• Change a numeric type for better precision
• Add or remove constraints such as NOT NULL

For example, you might expand a VARCHAR(50) to VARCHAR(150), change an INT to BIGINT, or enforce that a column cannot be NULL.

This is a bad idea if existing data conflicts with the new definition. For example, shrinking a column length can cause errors or data loss.

Prerequisites

• Access to the database
• Permission to alter tables
• Basic SQL knowledge
• A backup if working in production

Step-by-step instructions

Step 1: Modify the column definition

Use ALTER TABLE with the correct syntax for your database.

MySQL

Use MODIFY or CHANGE:

ALTER TABLE users
MODIFY email VARCHAR(150);

If you rename and modify at the same time:

ALTER TABLE users
CHANGE email user_email VARCHAR(150);

PostgreSQL

Use ALTER COLUMN:

ALTER TABLE users
ALTER COLUMN email TYPE VARCHAR(150);

To add or remove NOT NULL:

ALTER TABLE users
ALTER COLUMN email SET NOT NULL;

SQL Server

Use ALTER COLUMN:

ALTER TABLE users
ALTER COLUMN email NVARCHAR(150);

All systems require the full new column definition when changing the type.

What to look for

• The new type must be compatible with existing data
• Shrinking column length can fail if data exceeds the new limit
• Changing constraints may fail if current data violates them
• Some systems require dropping constraints before modifying the type

Examples you can copy

Example 1: Increase text length

You need longer usernames.

MySQL

ALTER TABLE users
MODIFY username VARCHAR(150);

PostgreSQL

ALTER TABLE users
ALTER COLUMN username TYPE VARCHAR(150);

Example 2: Change numeric precision

You want more decimal precision for prices.

MySQL

ALTER TABLE products
MODIFY price DECIMAL(12, 4);

PostgreSQL

ALTER TABLE products
ALTER COLUMN price TYPE DECIMAL(12, 4);

Example 3: Enforce NOT NULL

You decide that every order must have a status.

PostgreSQL

ALTER TABLE orders
ALTER COLUMN status SET NOT NULL;

SQL Server

ALTER TABLE orders
ALTER COLUMN status NVARCHAR(50) NOT NULL;

Make sure existing rows already contain valid values before running this command.

Common mistakes and how to fix them

Mistake 1: Shrinking a column with incompatible data

What you might do:

ALTER TABLE users
MODIFY username VARCHAR(10);

Why it fails: If existing usernames are longer than 10 characters, the database blocks the change.

Correct approach: Check current data length first:

SELECT MAX(LENGTH(username)) FROM users;

Then choose a safe size.

Mistake 2: Adding NOT NULL when NULL values exist

What you might do:

ALTER TABLE users
ALTER COLUMN email SET NOT NULL;

Why it fails: If any row contains NULL, the change is rejected.

Correct approach: Update rows first:

UPDATE users
SET email = 'unknown@example.com'
WHERE email IS NULL;

Then apply the constraint.

Mistake 3: Forgetting to restate the full definition in SQL Server

What you might do:

ALTER TABLE users
ALTER COLUMN email;

Why it fails: SQL Server requires the complete data type definition.

Correct approach:

ALTER TABLE users
ALTER COLUMN email NVARCHAR(150);

Always specify the full type.

Troubleshooting

• If you see “permission denied,” confirm you have ALTER privileges.
• If you see a type conversion error, verify existing data matches the new type.
• If the change fails due to constraints, review current values and update them before retrying.
• If indexes depend on the column, consider dropping and recreating them after modification.

Quick recap

• Use ALTER TABLE to modify a column
• Syntax differs across MySQL, PostgreSQL, and SQL Server
• Always specify the new data type fully
• Check existing data before shrinking size or adding constraints
• Update rows before enforcing NOT NULL
• Test structural changes before production