How to DROP TABLE in PostgreSQL

When should I use DROP TABLE?

Use DROP TABLE when a table is no longer needed and you want to free storage, clean schema clutter, or recreate it with a new design. The operation is irreversible, so verify that no critical data remains.

What is the exact syntax?

The basic form is DROP TABLE [IF EXISTS] table_name [, ...] [CASCADE | RESTRICT];. Add IF EXISTS to avoid errors if the table is absent. Choose CASCADE to drop dependent objects such as views or foreign-key constraints. RESTRICT (default) blocks the drop when dependencies exist.

How do I drop several tables at once?

List tables comma-separated: DROP TABLE IF EXISTS sales, customers CASCADE;. PostgreSQL processes all names in a single transaction, ensuring either all tables are removed or none if an error occurs.

How can I prevent accidental data loss?

Wrap the command in a transaction: BEGIN; DROP TABLE important_data; -- review -- ROLLBACK;. Commit only after confirming you want the change. Additionally, use role-based privileges to restrict DROP access.

What happens to indexes and sequences?

All indexes, table-owned sequences, and triggers vanish with the table. Sequences referenced from other tables remain unaffected unless CASCADE is specified and they are dependent.

Can I drop a temporary table?

Yes. Syntax is identical: DROP TABLE IF EXISTS temp_session;. Temporary tables are session-scoped; they disappear automatically at session end, but you can drop them sooner to reclaim memory.

Best practice: audit before dropping

Create a backup or run CREATE TABLE backup_table AS SELECT * FROM original; before dropping. This ensures you can restore quickly if you discover you needed the data.

Best practice: use IF EXISTS

Adding IF EXISTS keeps scripts idempotent, avoiding fatal errors during automated deployments when the table may already be gone.