Lesson 11 of 15 · 18 min
Dropping tables, views and columns safely
By the end of this lesson you can drop tables, views and columns, explain why PostgreSQL refuses a drop that other objects depend on, and choose between dropping dependents explicitly and using CASCADE.
Three statements remove things, and they remove very different things:
| Statement | What disappears | What remains |
|---|---|---|
DELETE FROM t WHERE … | the matching rows | the table and the other rows |
TRUNCATE t | all rows, quickly | the empty table and its definition |
DROP TABLE t | the table: definition, rows, indexes, constraints | nothing |
There are matching statements for other objects: DROP VIEW v, DROP INDEX i, and ALTER TABLE t DROP COLUMN c for a single column (its values are gone with it). None of them asks for confirmation.
IF EXISTS
DROP TABLE t fails with table "t" does not exist if there's nothing to drop. In clean-up and setup scripts that may run more than once, DROP TABLE IF EXISTS t turns that error into a notice and carries on. You can drop several objects in one statement: DROP TABLE IF EXISTS a, b, c;.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons