DROP COLUMN in PostgreSQL 🚀

The DROP COLUMN statement is used to remove a column from a PostgreSQL table.

1. Basic Syntax

ALTER TABLE table_name DROP COLUMN column_name;

2. Example: Drop a Single Column

ALTER TABLE users DROP COLUMN age;

(Removes the age column from the users table)

3. Drop Multiple Columns

ALTER TABLE users DROP COLUMN age, DROP COLUMN address;

(Removes both age and address columns)

4. Drop a Column Only If It Exists

ALTER TABLE users DROP COLUMN IF EXISTS age;

(Prevents errors if age does not exist)

5. Drop a Column with CASCADE (Removes Dependencies)

ALTER TABLE users DROP COLUMN profile_picture CASCADE;

(Removes profile_picture and any constraints or dependencies related to it)

6. Drop a Column from a Partitioned Table

ALTER TABLE users DROP COLUMN age CASCADE;

(Ensures that partitions linked to this table also lose the column)

7. Drop a Column from a JSONB Table

ALTER TABLE products DROP COLUMN details;

(Deletes a JSONB column that stores structured data)

8. Drop a Foreign Key Column

ALTER TABLE orders DROP COLUMN user_id CASCADE;

(Removes user_id and any foreign key constraints)

9. Check Table Structure After Dropping a Column

SELECT column_name FROM information_schema.columns WHERE table_name = 'users';

(Verifies if the column is successfully removed)

10. Alternative: Hide Instead of Dropping

Instead of dropping, you can rename an unused column:

ALTER TABLE users RENAME COLUMN age TO old_age;

(This avoids data loss in case you need it later)

Important Notes

• Dropping a column permanently deletes all its data.
• Use CASCADE if the column has foreign key constraints.
• If the column is indexed, dropping it removes the index.

Next Steps

✅ Modify columns with ALTER COLUMN
✅ Add new columns with ADD COLUMN
✅ Optimize queries with indexes