Chapter 12: PostgreSQL DROP COLUMN
PostgreSQL DROP COLUMN ☕🗑️
You’ve already learned how to:
- Create tables
- Add columns
- Modify columns (ALTER COLUMN)
- Update data
Now comes the opposite direction: removing a column you no longer need (maybe it was experimental, redundant after a refactor, or you’re cleaning up old legacy data to save space & simplify schema).
1. What does DROP COLUMN really do? (Honest teacher explanation – very important 2026 reality)
DROP COLUMN is a clause inside ALTER TABLE that tells PostgreSQL:
“Please make this column invisible and unusable from now on in this table. Remove any indexes, constraints, or statistics that depend only on this column. The actual data does NOT get immediately deleted from disk — PostgreSQL just marks the column as dropped in metadata.”
Key PostgreSQL behavior (still true in version 18.x in 2026):
- Very fast operation (usually seconds, even on huge tables) because it’s mostly a metadata change
- The physical data remains in the table files until rows are updated or the table is VACUUM FULL / rewritten
- New INSERT and UPDATE operations will not store values for the dropped column anymore
- Queries (SELECT *) will no longer see the column
- You cannot access the dropped column anymore (it’s gone from SQL point of view)
- Space is reclaimed gradually as rows get updated or table gets compacted
This “lazy deletion” is intentional — it avoids expensive full table rewrites on production databases with millions/billions of rows.
2. Basic syntax (what you’ll type 95% of the time)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
ALTER TABLE table_name DROP COLUMN column_name; -- Safer / production-friendly versions ALTER TABLE table_name DROP COLUMN IF EXISTS column_name; ALTER TABLE table_name DROP COLUMN column_name CASCADE; -- if there are dependencies ALTER TABLE table_name DROP COLUMN column_name RESTRICT; -- default behavior, fails on dependencies |
You can drop multiple columns in one statement:
|
0 1 2 3 4 5 6 7 8 |
ALTER TABLE students DROP COLUMN old_phone, DROP COLUMN temp_notes IF EXISTS; |
3. Real example – cleaning up our students table
Assume our current students table has these columns (after all previous lessons):
- id
- first_name
- last_name
- date_of_birth
- gpa
- phone_number (we added this earlier)
- fees_paid
- enrollment_year
- address (JSONB)
- profile_pic_url (maybe we added for fun, but now we use external storage)
Simple drop – remove a column we don’t need anymore
|
0 1 2 3 4 5 6 7 |
ALTER TABLE students DROP COLUMN profile_pic_url; |
→ Message: ALTER TABLE → Column gone from \d students → SELECT * FROM students no longer shows it
Safe drop – if you’re not sure it exists
|
0 1 2 3 4 5 6 7 |
ALTER TABLE students DROP COLUMN IF EXISTS old_experimental_column; |
→ No error even if column never existed → just a NOTICE if missing
Drop with CASCADE – when there are dependencies
Suppose phone_number is used in:
- A unique index
- A check constraint
- Or referenced in a view / materialized view
|
0 1 2 3 4 5 6 7 |
ALTER TABLE students DROP COLUMN phone_number CASCADE; |
→ PostgreSQL automatically drops:
- Any index on phone_number
- Any constraint involving only phone_number
- Any dependent views / materialized views (dangerous!)
→ Without CASCADE → error if dependencies exist
RESTRICT (default) behavior
|
0 1 2 3 4 5 6 7 |
ALTER TABLE students DROP COLUMN fees_paid RESTRICT; |
→ Fails if anything depends on fees_paid (e.g. a partial index or view) → Good for safety in scripts
4. What happens under the hood (very important to understand)
Before DROP COLUMN:
- Table has physical column → data stored on disk
After DROP COLUMN:
- Metadata marked “dropped” → column invisible to SQL
- Old data still physically there in heap files
- Future INSERT/UPDATE → no space used for that column
- Space reclamation:
- Happens gradually when rows are updated (new tuple without dropped column)
- Or force with VACUUM FULL students; (locks table, rewrites everything – use carefully!)
- Or use pg_repack / pg_squeeze extensions for online reclaim
On very large tables (e.g. 500 GB table with old unused column):
- DROP COLUMN → instant (metadata only)
- Real space free-up → can take days/weeks until most rows get touched
5. Common real-world scenarios & patterns
Pattern 1: Cleanup after migration/refactor
|
0 1 2 3 4 5 6 7 8 |
-- Old column migrated to JSONB address ALTER TABLE students DROP COLUMN IF EXISTS street_address CASCADE; ALTER TABLE students DROP COLUMN IF EXISTS city CASCADE; |
Pattern 2: Test in transaction first (very smart!)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
BEGIN; ALTER TABLE students DROP COLUMN phone_number CASCADE; -- Check \d students SELECT * FROM students LIMIT 3; -- If ok → COMMIT; -- If mistake → ROLLBACK; |
Pattern 3: Drop multiple legacy columns
|
0 1 2 3 4 5 6 7 8 9 |
ALTER TABLE orders DROP COLUMN legacy_tax_rate, DROP COLUMN old_discount_code, DROP COLUMN temp_import_flag; |
6. Warnings & best practices (from real production stories)
- Always use IF EXISTS in migration scripts → avoids failures on already-cleaned envs
- Prefer CASCADE carefully → can silently drop views / materialized views you forgot about
- Never DROP primary key / foreign key column without planning (use CASCADE but understand impact)
- Large tables → DROP COLUMN is fast, but VACUUM FULL or autovacuum will take time to reclaim space
- Backup first or test in staging/transaction
- After many DROP COLUMN → table can have “dead” column slots → max 1600 columns limit → if you hit it → need pg_dump + recreate table
7. Verify the drop
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Before / after \d students -- Column gone from queries SELECT * FROM students LIMIT 2; -- No more access possible SELECT phone_number FROM students; -- ERROR: column "phone_number" does not exist |
Your mini practice right now
- Drop the profile_pic_url column (if you added it earlier)
- Try dropping a non-existing column with IF EXISTS
- Add a dummy column → drop it with CASCADE (if it creates index first)
- Run \d students after each step
Next class?
Tell me:
- Want DROP TABLE or TRUNCATE next?
- How to add / drop constraints (UNIQUE, CHECK, FK) after creation?
- RENAME TABLE / RENAME COLUMN in detail?
- Or move to constraints / indexes / transactions?
Your guru is ready — what’s the next topic? 🚀
