PostgreSQL DROP TABLE

DROP TABLE Options

DROP TABLE IF EXISTS users;

DROP TABLE users CASCADE;

DROP TABLE users RESTRICT;

DROP TABLE t1, t2, t3;

🔹 Basic DROP TABLE

The simplest form drops a single table. If the table doesn't exist, PostgreSQL returns an error. Make absolutely sure you want to delete the table because this action is permanent and irreversible. All data, indexes, triggers, and constraints associated with the table are removed.

-- Drop a single table
DROP TABLE products;

-- This will cause an error if table doesn't exist
DROP TABLE non_existent_table;
-- ERROR: table "non_existent_table" does not exist

DROP TABLE
-- Table successfully removed

🔹 DROP TABLE IF EXISTS

IF EXISTS prevents errors when the table might not exist. This is useful in scripts that run multiple times or in different environments. PostgreSQL will skip the operation silently if the table doesn't exist, allowing your script to continue without interruption or error messages.

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

-- Useful in migration scripts
DROP TABLE IF EXISTS old_customers;
DROP TABLE IF EXISTS legacy_orders;

-- Create fresh table after dropping
DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

NOTICE: table "temp_data" does not exist, skipping
DROP TABLE

🔹 DROP Multiple Tables

You can drop multiple tables in a single statement by listing them separated by commas. This is more efficient than executing separate DROP statements. All tables are dropped in the order specified, and if any table fails, the entire operation may be rolled back.

-- Drop multiple tables at once
DROP TABLE orders, order_items, customers;

-- Drop multiple tables safely
DROP TABLE IF EXISTS 
    temp_table1, 
    temp_table2, 
    temp_table3;

-- Drop related tables together
DROP TABLE IF EXISTS 
    user_sessions,
    user_preferences,
    user_logs;

DROP TABLE
-- All specified tables have been removed

🔹 DROP TABLE CASCADE

CASCADE automatically drops objects that depend on the table, including foreign key constraints from other tables, views, and triggers. This is powerful but dangerous because it can affect multiple tables. Use CASCADE carefully and understand all dependencies before executing this command to avoid unintended data loss.

-- Drop table and all dependent objects
DROP TABLE departments CASCADE;

-- This will also drop:
-- - Foreign keys in employees table referencing departments
-- - Views that use the departments table
-- - Triggers on the departments table

-- Safe cascade drop
DROP TABLE IF EXISTS categories CASCADE;

NOTICE: drop cascades to constraint employees_dept_fk on table employees
DROP TABLE

🔹 DROP TABLE RESTRICT

RESTRICT is the default behavior that prevents dropping a table if other objects depend on it. PostgreSQL will return an error listing the dependencies. This safety feature protects your database integrity by ensuring you don't accidentally break relationships between tables or remove tables that views or functions rely on.

-- Prevent drop if dependencies exist (default)
DROP TABLE departments RESTRICT;

-- Same as:
DROP TABLE departments;

-- If employees table has foreign key to departments:
-- ERROR: cannot drop table departments because other objects depend on it
-- DETAIL: constraint employees_dept_fk on table employees depends on table departments
-- HINT: Use DROP ... CASCADE to drop the dependent objects too.

ERROR: cannot drop table departments because other objects depend on it

🔹 Checking Dependencies

Before dropping a table, check what depends on it to avoid surprises. Query system catalogs to find foreign keys, views, and other objects that reference your table. This information helps you decide whether to use CASCADE or manually handle dependencies before dropping the table safely.

-- Find foreign keys referencing a table
SELECT
    tc.table_name, 
    kcu.column_name,
    ccu.table_name AS foreign_table_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 = 'departments';

-- Find views using a table
SELECT DISTINCT table_name
FROM information_schema.view_table_usage
WHERE view_table_name = 'departments';

🔹 Safety Best Practices

Always backup your database before dropping tables. Use transactions when possible to allow rollback. Test DROP statements in development first. Document why tables are being dropped. Consider archiving data instead of dropping tables if you might need the information later for auditing or historical analysis purposes.

-- Safe drop pattern with backup
BEGIN;
    -- Create backup table
    CREATE TABLE customers_backup AS SELECT * FROM customers;
    
    -- Verify backup
    SELECT COUNT(*) FROM customers_backup;
    
    -- Drop original table
    DROP TABLE customers;
    
    -- If everything is okay
COMMIT; -- or ROLLBACK if there's a problem