DROP

---

In PostgreSQL, the DROP clause is part of DDL (Data Definition Language). It is the most destructive command in your toolkit because, unlike DELETE, it doesn't just remove data—it removes the entire structure and its associated metadata from the system catalog.

---

Dropping Tables

The most common usage. It deletes the table definition, all its rows, and all its indexes.

DROP TABLE users;

The "Safety First" Pattern

If you run a DROP on a table that doesn't exist, Postgres will throw an error and stop your script. In Data Engineering pipelines, we almost always use the IF EXISTS modifier to make scripts idempotent (runnable multiple times without failing).

DROP TABLE IF EXISTS staging_logs;

---

Dependency Management: CASCADE vs. RESTRICT

This is the most critical part for your notes. Tables often have relationships (Views, Foreign Keys, etc.).

• RESTRICT (Default): If another object depends on this table (like a View that references it), Postgres will refuse to drop it and throw an error.

• CASCADE: Automatically drops any objects that depend on the table.

Warning: Be extremely careful with CASCADE. Dropping one table could unintentionally delete 5 views and 2 triggers that you forgot were connected.

DROP TABLE customers CASCADE;

---

The DROP command follows a consistent pattern across the entire database:

Object	Command
Database	DROP DATABASE analytics_db;
Schema	DROP SCHEMA staging_area;
View	DROP VIEW monthly_report;
Index	DROP INDEX idx_user_email;
Constraint	ALTER TABLE users DROP CONSTRAINT pk_users;
Column	ALTER TABLE users DROP COLUMN middle_name;

---

DROP vs. TRUNCATE vs. DELETE

For a Data Engineer, choosing the right "removal" method is about performance and intent.

Action	DELETE	TRUNCATE	DROP
Target	Specific Rows.	All Rows.	The Table itself.
Speed	Slowest (Logs everything).	Fast (Metadata only).	Fast (Metadata only).
Recoverable?	Yes (Rollback).	Yes (Rollback).	Yes (Rollback).
Space	Reclaimed by Vacuum.	Reclaimed immediately.	Reclaimed immediately.

---

Dropping Columns (The Storage Catch)

When you run ALTER TABLE ... DROP COLUMN, Postgres does not physically remove the data from the disk immediately. It simply marks the column as "invisible" in the system catalog.

• The disk space is only reclaimed after a VACUUM FULL or when rows are updated.

• DE Tip: If you drop a large column to save space, you won't see that space return to the OS until you perform maintenance.

---

Visualizing the Impact

Summary

• Always use IF EXISTS in automation scripts.

• Understand the CASCADE path before execution.

• Use DROP COLUMN carefully on large tables; it’s a metadata change that leaves "ghost" data on the disk until a rewrite happens.

---