|
Getting your Trinity Audio player ready...
|
When you start working on real-world databases, one thing becomes clear:
👉 Data structure keeps changing.
- New column required
- Wrong data type selected
- Text stored instead of date
- Integer not large enough
- Column name incorrect
And this is where ALTER TABLE becomes your best friend.
What is ALTER TABLE?
ALTER TABLE is used to modify an existing table structure.
You can:
- Add new columns
- Modify column data types
- Rename columns
- Drop columns
- Add or remove constraints
Think of it like renovating a house without rebuilding it.
Adding a New Column
Suppose we forgot to add last_login in the employees table.
PostgreSQL
ALTER TABLE employees
ADD COLUMN last_login TIMESTAMP;MySQL
ALTER TABLE employees
ADD COLUMN last_login DATETIME;✔ PostgreSQL uses TIMESTAMP
✔ MySQL commonly uses DATETIME
Changing Data Type (Very Important for Data Cleaning)
This is extremely common in Data Analyst work.
Sometimes:
- Dates are stored as TEXT
- Numbers stored as VARCHAR
- IDs stored as INT but should be BIGINT
Let’s fix real scenarios.
Case 1: Convert TEXT to TIMESTAMP / DATETIME
Suppose in sales table, sale_date was stored as TEXT like:
05-Jan-2024 10:30 AMWe need to convert it to proper date-time format.
MySQL (Using STR_TO_DATE)
ALTER TABLE sales
MODIFY sale_date DATETIME;If conversion is required:
UPDATE sales
SET sale_date = STR_TO_DATE(sale_date, '%d-%b-%Y %h:%i %p')
WHERE sale_date IS NOT NULL;✔ STR_TO_DATE() converts string to DATETIME
✔ Used heavily in cleaning Excel-imported data
Read More: Data Types in SQL
PostgreSQL (Using TO_TIMESTAMP)
Postgres requires USING clause:
ALTER TABLE sales
ALTER COLUMN sale_date TYPE TIMESTAMP
USING TO_TIMESTAMP(sale_date, 'DD-Mon-YYYY HH12:MI AM');✔ TO_TIMESTAMP() converts text to timestamp
✔ Format must match exactly
This is VERY common in real projects.
Case 2: Change INTEGER to BIGINT
Suppose your sales.id column is INTEGER.
But now your company has 50 million records.
INTEGER may overflow.
So we upgrade to BIGINT.
PostgreSQL
ALTER TABLE sales
ALTER COLUMN id TYPE BIGINT;MySQL
ALTER TABLE sales
MODIFY id BIGINT;Why BIGINT?
| Type | Range |
|---|---|
| INT | ~2 billion |
| BIGINT | ~9 quintillion |
Large production systems often use BIGINT.
Case 3: Convert VARCHAR to INTEGER
Suppose customers.pincode was stored as VARCHAR but should be numeric.
PostgreSQL
ALTER TABLE customers
ALTER COLUMN pincode TYPE INTEGER
USING pincode::INTEGER;MySQL
ALTER TABLE customers
MODIFY pincode INT;⚠ Make sure no text characters exist before conversion.
Case 4: Rename Column
Suppose products.margin_percent needs better name.
PostgreSQL
ALTER TABLE products
RENAME COLUMN margin_percent TO profit_margin_percent;MySQL
ALTER TABLE products
CHANGE margin_percent profit_margin_percent DECIMAL(5,2);(MySQL requires datatype again.)
Case 5: Drop Column
Suppose employees.commission_pct is no longer required.
PostgreSQL
ALTER TABLE employees
DROP COLUMN commission_pct;MySQL
ALTER TABLE employees
DROP COLUMN commission_pct;Same syntax here ✅
Case 6: Add Constraint Later
Suppose we forgot to add NOT NULL on products.product_name.
PostgreSQL
ALTER TABLE products
ALTER COLUMN product_name SET NOT NULL;MySQL
ALTER TABLE products
MODIFY product_name VARCHAR(200) NOT NULL;Real Data Analyst Scenario
In real life:
- Excel import gives date in text format
- CSV import makes numbers VARCHAR
- IDs overflow INT limit
- NULL values break reports
- Wrong column names confuse BI tools
And you don’t recreate tables.
You fix them using:
✔ ALTER TABLE
✔ UPDATE
✔ Type conversion functions
This is why ALTER TABLE is critical for Data Analysts.
PostgreSQL vs MySQL Quick Difference
| Operation | PostgreSQL | MySQL |
|---|---|---|
| Change datatype | ALTER COLUMN TYPE | MODIFY |
| Rename column | RENAME COLUMN | CHANGE |
| Convert text to timestamp | TO_TIMESTAMP | STR_TO_DATE |
| Casting | ::INTEGER | CAST() |
Best Practice Tips
- Always take backup before altering production tables
- Test conversion in SELECT before ALTER
- Check for NULL values before adding NOT NULL
- Use BIGINT for large transaction tables
- Be careful while converting TEXT to numeric
Final Thoughts
If SELECT is about reading data,
INSERT is about adding data,
UPDATE is about modifying data,
Then ALTER TABLE is about evolving your database structure.
In real-world projects — especially in MIS, reporting, and analytics —
you will use ALTER TABLE frequently to clean and optimize data.
And once you master it,
you are no longer just writing queries —
you are designing systems.