PostgreSQL DROP COLUMN

🧱 PostgreSQL DROP COLUMN (Beginner → Advanced)

In PostgreSQL DROP COLUMN is used to permanently remove a column from an existing table.
This operation deletes the column and all its data.

⚠️ Irreversible – once dropped, the column data cannot be recovered.

1️⃣ What is DROP COLUMN?

DROP COLUMN removes a specific column from a table.

✔ Column removed
✔ Data removed
✔ Table remains intact

2️⃣ Basic DROP COLUMN Example ⭐

Table: students

Drop age column

📌 Resulting table:
| id | name | marks |

3️⃣ DROP COLUMN IF EXISTS ⭐ (Safe Practice)

Avoids error if the column does not exist.

✔ Prevents runtime errors
✔ Recommended for scripts & migrations

4️⃣ Dropping Multiple Columns at Once 🔥

✔ Efficient
✔ Cleaner migrations

5️⃣ DROP COLUMN with CASCADE ⭐⭐

Used when other objects depend on the column (indexes, views, constraints).

✔ Removes:

• dependent indexes

• constraints

• views using the column

⚠️ Use carefully

6️⃣ DROP COLUMN with RESTRICT (Default)

✔ Prevents drop if dependencies exist
✔ Default behavior in PostgreSQL

7️⃣ DROP COLUMN and Transactions 🔁

❌ Column is NOT restored

📌 DROP COLUMN is auto-commit
📌 Cannot be rolled back

8️⃣ DROP COLUMN vs DELETE ⭐ (Important Difference)

✔ Use DROP COLUMN for schema changes
✔ Use DELETE for data cleanup

9️⃣ Check Before Dropping a Column ⭐

✔ Always verify column existence
✔ Take backup if column is important

🔟 Real-Life Use Cases

• Removing unused columns

• Schema cleanup

• Database refactoring

• Removing deprecated fields

• Optimizing table design

1️⃣1️⃣ Common Mistakes ❌

❌ Dropping column instead of deleting data
❌ Forgetting IF EXISTS
❌ Using CASCADE unintentionally
❌ Running in production without backup

📌 Interview Questions (Must Prepare)

1. How to drop a column in PostgreSQL?

2. Can DROP COLUMN be rolled back?

3. Difference between DROP COLUMN and DELETE?

4. What does CASCADE do in DROP COLUMN?

5. Can we drop multiple columns at once?

6. What happens to data in dropped column?

✅ Summary

✔ DROP COLUMN removes a column permanently
✔ Uses ALTER TABLE
✔ IF EXISTS prevents errors
✔ CASCADE removes dependent objects
✔ Cannot be rolled back
✔ Critical for schema design & interviews