PostgreSQL DROP COLUMN

PostgreSQL Tutorial

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.

 What is DROP COLUMN?

DROP COLUMN removes a specific column from a table.

  •  Column removed
  •  Data removed
  •  Table remains intact

 Basic DROP COLUMN Example

Table: students

idnamemarksage

Drop age column

 Resulting table:
| id | name | marks |

DROP COLUMN IF EXISTS (Safe Practice)

Avoids error if the column does not exist.

  •  Prevents runtime errors
  •  Recommended for scripts & migrations

 Dropping Multiple Columns at Once

  •  Efficient
  • Cleaner migrations

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

DROP COLUMN with RESTRICT (Default)

  •  Prevents drop if dependencies exist
  •  Default behavior in PostgreSQL

DROP COLUMN and Transactions

  •  Column is NOT restored
  • DROP COLUMN is auto-commit
  •  Cannot be rolled back

DROP COLUMN vs DELETE (Important Difference)

FeatureDROP COLUMNDELETE
Removes column YesNo
Removes rows NoYes
Removes data permanentlyYesConditional
Rollback NoYes
  •  Use DROP COLUMN for schema changes
  •  Use DELETE for data cleanup

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

 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

You may also like...