How to Change Data Type in SQL

Use a data type change when a column’s current type no longer fits the real data, such as expanding text length, converting strings to numbers, or upgrading dates and timestamps. This is a common schema migration task.

What you’ll build or solve

You’ll learn how to change a column’s data type in SQL using ALTER TABLE. You’ll also know how to validate existing data before applying the change.

When this approach works best

This approach is the right choice when the schema should evolve without recreating the whole table.

Common real-world scenarios include:

• INT to BIGINT
• VARCHAR(50) to VARCHAR(255)
• Text dates to real dates
• Decimal precision upgrades
• Boolean normalization

This is a bad idea when existing values cannot safely convert to the new type.

Prerequisites

You only need:

• ALTER TABLE permissions
• A clear target data type
• Validation that current values are convertible

Step-by-step instructions

Step 1: Use ALTER TABLE with your database syntax

The exact syntax differs slightly by engine.

PostgreSQL

ALTER TABLE users
ALTER COLUMN age TYPE BIGINT;

MySQL

ALTER TABLE users
MODIFY age BIGINT;

SQL Server

ALTER TABLE users
ALTER COLUMN age BIGINT;

Always preview problematic rows first.

SELECT age
FROM users
WHERE age !~ '^\d+$';

This helps catch invalid values before conversion.

What to look for:

• Syntax varies by database
• Existing data must be convertible
• Great for schema evolution
• Validate before altering
• Large tables may lock during migration

Examples you can copy

Expand text length

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

Upgrade integer size

ALTER TABLE orders
ALTER COLUMN order_id TYPE BIGINT;

Convert text to date

ALTER TABLE events
ALTER COLUMN event_date TYPE DATE;

Common mistakes and how to fix them

Mistake 1: Converting incompatible existing values

What the reader might do:

Convert text 'unknown' into INT.

Why it breaks: invalid values cause the migration to fail.

Corrected approach:

Clean or map invalid rows first.

Mistake 2: Forgetting dependent constraints or indexes

What the reader might do:

Alter a foreign key column directly.

Why it breaks: related constraints may fail.

Corrected approach:

Update related columns and constraints together.

Mistake 3: Running large blocking migrations in production hours

What the reader might do:

Alter a massive table live.

Why it breaks: long locks can impact the app.

Corrected approach:

Use phased migrations or off-peak deployment windows.

Troubleshooting

If the migration fails, inspect incompatible rows.

If foreign keys break, align parent and child types.

If the table locks too long, use staged migrations.

If precision changes, verify downstream calculations.

Quick recap

• Use ALTER TABLE to change column types
• Syntax differs by database
• Validate existing values first
• Watch foreign keys and locks
• Use phased rollouts for large tables