PostgreSQL ALTER COLUMN

🧱 PostgreSQL ALTER COLUMN (Beginner → Advanced)

In PostgreSQL ALTER COLUMN is used to modify an existing column’s definition—such as its data type, default value, nullability, or storage properties—without dropping the table.

1️⃣ What is ALTER COLUMN?

ALTER COLUMN changes the properties of a column in an existing table.



 

✔ Column exists
✔ Table remains intact
✔ Data may be transformed (depending on action)

2️⃣ Change Column Data Type ⭐ (Most Common)



 

With explicit casting (recommended)



 

📌 Use USING when PostgreSQL can’t auto-convert safely.

3️⃣ Set / Drop DEFAULT Value ⭐

Set default



 

Drop default



 

✔ Affects future INSERTs only

4️⃣ Set / Drop NOT NULL Constraint ⭐

Make column NOT NULL



 

⚠️ Fails if any existing rows have NULL.

Allow NULLs



 

5️⃣ Rename a Column (Related Operation)



 

✔ Simple and safe
✔ No data loss

6️⃣ Change Column Length (e.g., VARCHAR) ⭐



 

✔ Increasing size is safe
⚠️ Decreasing size may fail if data exceeds new limit

7️⃣ Change Column Data Type with Constraints ⭐⭐

Example: TEXT → INTEGER



 

📌 Ensure all values are convertible before running.

8️⃣ Multiple ALTER COLUMN in One Statement 🔥



 

✔ Cleaner migrations
✔ Fewer table locks

9️⃣ Performance & Locking ⚠️

  • ALTER COLUMN TYPE may rewrite the table

  • Large tables → long locks

  • Best practices:

    • Run during low traffic

    • Test on staging

    • Consider batching or shadow columns for huge tables

🔟 ALTER COLUMN vs ADD/DROP COLUMN ⭐

Operation Use Case
ALTER COLUMN Modify existing column
ADD COLUMN Add new column
DROP COLUMN Remove column permanently

1️⃣1️⃣ Common Mistakes ❌

❌ Changing type without USING
❌ Setting NOT NULL when NULLs exist
❌ Shrinking column length without checking data
❌ Running on production without testing

📌 Interview Questions (Must Prepare)

  1. How do you change a column’s data type in PostgreSQL?

  2. What is the purpose of USING?

  3. Difference between SET DEFAULT and NOT NULL?

  4. Can ALTER COLUMN be rolled back?

  5. Does ALTER COLUMN TYPE lock the table?

  6. How to rename a column?

✅ Summary

✔ ALTER COLUMN modifies column properties
✔ Common actions: TYPE, SET/DROP DEFAULT, SET/DROP NOT NULL
✔ Use USING for safe casting
✔ Can impact performance on large tables
✔ Essential for schema evolution & interviews

You may also like...