Topic 7 of 52
DROP, TRUNCATE
Overview
DROP TABLE permanently deletes a table and all its data. TRUNCATE removes all rows but keeps the table structure. Both are irreversible (without a backup) and must be used with extreme caution in production.
Syntax
sql
-- DROP TABLE: deletes table and all data permanently
DROP TABLE employees; -- error if table doesn't exist
DROP TABLE IF EXISTS employees; -- safe version, no error
-- DROP with CASCADE: also drops dependent objects (foreign keys, views)
DROP TABLE users CASCADE; -- drops tables that reference users!
DROP TABLE users RESTRICT; -- default: fails if dependencies exist
-- TRUNCATE: removes all rows, keeps structure
TRUNCATE TABLE audit_log;
TRUNCATE TABLE sessions RESTART IDENTITY; -- resets auto-increment
TRUNCATE TABLE orders, order_items CASCADE; -- truncate related tables
-- Comparison: DELETE vs TRUNCATE
DELETE FROM logs WHERE created_at < '2024-01-01'; -- can WHERE filter
TRUNCATE TABLE logs; -- no WHERE, all rowsCommon Pitfalls
- DROP TABLE is permanent — always backup before dropping. In production, rename the table first and drop after verification.
- TRUNCATE cannot be filtered with WHERE — it removes ALL rows. Use DELETE FROM table WHERE condition for selective removal.
- Interview tip: TRUNCATE resets SERIAL/auto-increment sequences when using RESTART IDENTITY. DELETE does NOT reset sequences.
Real-World Example
Safe schema cleanup workflow for testing environments:
example
sql
-- Development: reset all test data
-- NEVER run this on production!
-- Safe drop with checks
DO $$
BEGIN
IF EXISTS (SELECT FROM pg_tables WHERE tablename = 'test_orders') THEN
DROP TABLE test_orders CASCADE;
RAISE NOTICE 'Dropped test_orders';
END IF;
END $$;
-- Truncate multiple tables in correct order (respect FK constraints)
TRUNCATE TABLE order_items, orders, users RESTART IDENTITY CASCADE;
-- TRUNCATE is MUCH faster than DELETE for clearing all rows
-- DELETE: logs each row deletion → slow for millions of rows
-- TRUNCATE: deallocates data pages → instant for any size
-- Schema migration: rename before drop (safer)
ALTER TABLE old_feature_flags RENAME TO _deprecated_feature_flags;
-- ... test that nothing breaks ...
-- DROP TABLE _deprecated_feature_flags; -- only after verification