PostgreSQL DROP TABLE

PostgreSQL Tutorial

PostgreSQL DROP TABLE (Beginner → Advanced)

In PostgreSQL DROP TABLE is used to permanently delete a table from the database, including:

  • all data (rows)

  • table structure

  • indexes

  • constraints

  • triggers

 This action is irreversible.

 What is DROP TABLE?

DROP TABLE removes a table completely from the database.

  •  Table is deleted
  •  Data is deleted
  •  Structure is deleted

 Basic DROP TABLE Example

After execution:

  • students table no longer exists

  • Cannot run SELECT * FROM students

DROP TABLE IF EXISTS (Safe Practice)

Avoids error if the table does not exist.

  •  Prevents runtime errors
  •  Recommended in scripts

Dropping Multiple Tables at Once

  •  Deletes multiple tables in one command

DROP TABLE with CASCADE

Used when other objects depend on the table (foreign keys, views).

Drops:

  • dependent foreign key constraints

  • views referencing the table

 Dangerous if used blindly

DROP TABLE with RESTRICT (Default)

  •  Prevents drop if dependencies exist
  •  Default behavior in PostgreSQL

DROP TABLE vs DELETE vs TRUNCATE (Very Important)

FeatureDROP TABLETRUNCATEDELETE
Removes table structure Yes No No
Removes all data YesYes Yes
WHERE clause No No Yes
Rollback possible No LimitedYes
SpeedFastestVery FastSlower
  •  Use DROP TABLE when table is no longer needed

Transaction Behavior

  •  Table is NOT restore
  • DROP TABLE is auto-commit in PostgreSQL
  •  Cannot be rollback

 Check Before Dropping a Table

  •  Always verify data
  •  Take backup if needed

 Real-Life Use Cases

  • Removing temporary tables

  • Cleaning old/unused tables

  • Resetting development databases

  • Database migration scripts

 Common Mistakes

  •  Dropping table instead of deleting rows
  •  Forgetting IF EXISTS
  •  Using CASCADE unintentionally
  •  Running in production without backup

Interview Questions (Must Prepare)

  1. What does DROP TABLE do in PostgreSQL?

  2. Difference between DROP, TRUNCATE, and DELETE

  3. What is CASCADE in DROP TABLE?

  4. Can DROP TABLE be rolled back?

  5. What happens to dependent objects?

 Summary

  • DROP TABLE permanently deletes a table
  •  Removes data + structure
  • IF EXISTS prevents errors
  • CASCADE removes dependent objects
  •  Cannot be rollback
  •  Use carefully, especially in production

You may also like...