PostgreSQL Drop Table

Overview

The DROP TABLE statement permanently removes a table and all its data from the database. This is an irreversible operation, so make sure to backup important data before executing.

DROP TABLE Statement

Basic Syntax

DROP TABLE table_name;

Simple Examples

-- Drop a table
DROP TABLE users;

-- Safe drop (no error if table doesn't exist)
DROP TABLE IF EXISTS users;

Complete Syntax

DROP TABLE [IF EXISTS] table_name [, ...]
    [CASCADE | RESTRICT];

Drop Options

CASCADE vs RESTRICT

-- CASCADE: Also drop dependent objects
DROP TABLE departments CASCADE;

-- RESTRICT: Refuse to drop if dependencies exist (default)
DROP TABLE departments RESTRICT;

Difference Explained

Option	Description
CASCADE	Automatically drops dependent objects (views, foreign keys, etc.)
RESTRICT	Refuses to drop if dependent objects exist

Dropping Multiple Tables

-- Drop multiple tables at once
DROP TABLE IF EXISTS table1, table2, table3;

-- Drop in dependency order
DROP TABLE order_items;
DROP TABLE orders;
DROP TABLE users;

-- Or use CASCADE to drop all at once
DROP TABLE users CASCADE;

Practical Examples

Cleaning Up Test Data

-- Drop all test tables
DROP TABLE IF EXISTS test_users CASCADE;
DROP TABLE IF EXISTS test_orders CASCADE;
DROP TABLE IF EXISTS test_products CASCADE;

Resetting Database Structure

-- Drop old table and recreate
DROP TABLE IF EXISTS users CASCADE;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Viewing Table Dependencies

Before dropping a table, check which objects depend on it:

-- View foreign key dependencies
SELECT
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
    AND ccu.table_name = 'users';

-- View views that depend on the table
SELECT viewname
FROM pg_views
WHERE definition LIKE '%users%';

Important Notes

1. Data loss: DROP TABLE permanently deletes all data
2. Index deletion: All indexes on the table are also deleted
3. Trigger deletion: Triggers associated with the table are deleted
4. Foreign key constraints: CASCADE affects foreign keys referencing the table
5. Permission required: Requires table owner or superuser privileges

Difference from TRUNCATE

Feature	DROP TABLE	TRUNCATE
Deletes data	Yes	Yes
Deletes structure	Yes	No
Deletes indexes	Yes	No
Rollback	No	Yes (in transaction)
Fires triggers	No	Configurable
Resets sequences	Table is deleted	Optional

Common Errors

Table Does Not Exist

-- Error message
ERROR: table "users" does not exist

-- Solution: Use IF EXISTS
DROP TABLE IF EXISTS users;

Dependent Objects Exist

-- Error message
ERROR: cannot drop table users because other objects depend on it

-- Solution: Use CASCADE or drop dependencies first
DROP TABLE users CASCADE;

Safe Drop Procedure

-- 1. Check if table exists
SELECT EXISTS (
    SELECT FROM information_schema.tables
    WHERE table_name = 'users'
);

-- 2. Check dependent objects
SELECT dependent_ns.nspname as dependent_schema,
       dependent_view.relname as dependent_view
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN pg_namespace dependent_ns ON dependent_view.relnamespace = dependent_ns.oid
WHERE pg_depend.refobjid = 'users'::regclass;

-- 3. Backup data (if needed)
CREATE TABLE users_backup AS SELECT * FROM users;

-- 4. Drop table
DROP TABLE users CASCADE;