DROP TABLE: Removing Database Structures

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- Basic DROP TABLE Syntax
DROP TABLE table_name;
 
-- Drop with explicit behavior (if dependencies exist)
DROP TABLE table_name CASCADE;    -- Also drop dependent objects
DROP TABLE table_name RESTRICT;   -- Fail if dependencies exist (default)
 
-- Conditional drop (prevent error if table doesn't exist)
DROP TABLE IF EXISTS table_name;
 
-- Drop multiple tables in one statement
DROP TABLE table1, table2, table3;
 
-- Combined: Conditional drop with cascade
DROP TABLE IF EXISTS obsolete_log, temp_data, staging_import CASCADE;
 
-- Example: Simple table drop
DROP TABLE temporary_calculations;
 
-- Example: Drop with schema qualification
DROP TABLE myschema.archived_data;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97

-- ============================================
-- Setup: Create tables with dependencies
-- ============================================
CREATE TABLE departments (
    dept_id     INTEGER PRIMARY KEY,
    dept_name   VARCHAR(100) NOT NULL
);
 
CREATE TABLE employees (
    emp_id      INTEGER PRIMARY KEY,
    emp_name    VARCHAR(100) NOT NULL,
    dept_id     INTEGER REFERENCES departments(dept_id)  -- Foreign key!
);
 
CREATE VIEW dept_employee_count AS
SELECT d.dept_name, COUNT(e.emp_id) as employee_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;
 
 
-- ============================================
-- RESTRICT (Default): Prevents accidental drops
-- ============================================
-- This fails because employees.dept_id references departments
DROP TABLE departments;
-- ERROR: cannot drop table departments because other objects depend on it
-- DETAIL: constraint employees_dept_id_fkey on table employees depends on table departments
--         view dept_employee_count depends on table departments
 
DROP TABLE departments RESTRICT;  -- Same result, explicit
 
 
-- ============================================
-- CASCADE: Drops dependencies too
-- ============================================
-- This succeeds, but ALSO drops:
-- - The foreign key constraint on employees.dept_id
-- - The view dept_employee_count
DROP TABLE departments CASCADE;
-- NOTICE: drop cascades to view dept_employee_count
-- NOTICE: drop cascades to constraint employees_dept_id_fkey on table employees
 
 
-- ============================================
-- What CASCADE drops (by object type)
-- ============================================
-- CASCADE affects:
-- ✓ Foreign key constraints referencing the table
-- ✓ Views that SELECT from the table
-- ✓ Rules associated with the table
-- ✓ Policies (row-level security) on the table
-- ✓ Triggers on the table
-- ✓ Indexes on the table
 
-- CASCADE does NOT drop:
-- ✗ Tables that have foreign keys pointing TO this table
--   (the FK constraint is dropped, but the table remains)
-- ✗ Stored procedures that reference the table (in most DBs)
-- ✗ Application code that queries the table
 
 
-- ============================================
-- Finding Dependencies Before Dropping
-- ============================================
-- PostgreSQL: List ALL dependencies of a table
SELECT 
    dependent.relname AS dependent_object,
    dependent.relkind AS object_type,
    pg_describe_object(dependent.oid::regclass::oid, 0, 0) AS description
FROM pg_depend d
JOIN pg_class dependent ON d.objid = dependent.oid
JOIN pg_class source ON d.refobjid = source.oid
WHERE source.relname = 'your_table_name'
  AND d.deptype IN ('n', 'a');
 
-- PostgreSQL: Find foreign keys referencing a table
SELECT 
    tc.table_name AS referencing_table,
    kcu.column_name AS referencing_column,
    tc.constraint_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu 
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu 
    ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
    AND ccu.table_name = 'your_table_name';
 
-- PostgreSQL: Find views that depend on a table
SELECT DISTINCT dependent_view.relname AS view_name
FROM pg_depend d
JOIN pg_rewrite r ON d.objid = r.oid
JOIN pg_class dependent_view ON r.ev_class = dependent_view.oid
JOIN pg_class source_table ON d.refobjid = source_table.oid
WHERE source_table.relname = 'your_table_name'
    AND dependent_view.relkind = 'v';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

-- ============================================
-- The Problem: Dropping Non-Existent Tables Fails
-- ============================================
DROP TABLE phantom_table;
-- ERROR: table "phantom_table" does not exist
 
-- This breaks deployment scripts that should be re-runnable
 
 
-- ============================================
-- The Solution: IF EXISTS
-- ============================================
DROP TABLE IF EXISTS phantom_table;
-- NOTICE: table "phantom_table" does not exist, skipping
-- (No error, script continues)
 
 
-- ============================================
-- Use Cases for IF EXISTS
-- ============================================
 
-- 1. Clean-slate deployment scripts
DROP TABLE IF EXISTS user_sessions;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS roles;
-- Now safe to CREATE fresh tables
 
-- 2. Migration rollback scripts
-- "Down" migration that reverses table creation
DROP TABLE IF EXISTS new_feature_data;
 
-- 3. Test fixture cleanup
DROP TABLE IF EXISTS test_orders CASCADE;
DROP TABLE IF EXISTS test_customers CASCADE;
 
-- 4. Temporary table cleanup (defensive)
DROP TABLE IF EXISTS tmp_import_staging;
DROP TABLE IF EXISTS tmp_calculation_results;
 
 
-- ============================================
-- Combining IF EXISTS with CASCADE
-- ============================================
-- Maximum flexibility for cleanup scripts
DROP TABLE IF EXISTS staging_data CASCADE;
 
-- Works whether table exists or not
-- If exists, drops table and dependencies
-- If not exists, silently continues
 
 
-- ============================================
-- Alternative: Conditional Logic (PL/pgSQL)
-- ============================================
-- When you need more control than IF EXISTS provides
DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.tables 
        WHERE table_name = 'old_data' AND table_schema = 'public'
    ) THEN
        -- Log before dropping
        RAISE NOTICE 'Dropping table old_data with % rows', 
            (SELECT COUNT(*) FROM old_data);
        
        DROP TABLE old_data;
        RAISE NOTICE 'Table old_data dropped successfully';
    ELSE
        RAISE NOTICE 'Table old_data does not exist, nothing to drop';
    END IF;
END $$;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

-- ============================================
-- DELETE: Row-by-row removal with full control
-- ============================================
-- Remove specific rows matching condition
DELETE FROM orders WHERE status = 'cancelled' AND created_at < '2023-01-01';
 
-- Remove all rows (slow for large tables)
DELETE FROM temp_log;
 
-- Triggers fire for each deleted row
-- Full transaction logging (can make log files huge)
-- Can be selective with WHERE clause
 
 
-- ============================================
-- TRUNCATE: Fast bulk removal, keeps structure
-- ============================================
-- Remove all rows blazingly fast
TRUNCATE TABLE temp_log;
 
-- Truncate multiple tables
TRUNCATE TABLE log_2022, log_2023, log_2024;
 
-- Reset identity/sequence counters (PostgreSQL)
TRUNCATE TABLE users RESTART IDENTITY;
 
-- Keep identity counters (PostgreSQL)
TRUNCATE TABLE orders CONTINUE IDENTITY;
 
-- Truncate with CASCADE (also truncates child tables via FK)
TRUNCATE TABLE parent_table CASCADE;
 
 
-- ============================================
-- DROP TABLE: Complete removal
-- ============================================
-- Remove table entirely
DROP TABLE obsolete_feature_data;
 
-- Table no longer exists—any query referencing it fails
SELECT * FROM obsolete_feature_data;  
-- ERROR: relation "obsolete_feature_data" does not exist
 
 
-- ============================================
-- Choosing the Right Command
-- ============================================
 
-- Scenario 1: Remove old log entries but keep recent ones
DELETE FROM application_logs WHERE log_date < '2024-01-01';
-- Use DELETE: Selective removal needed
 
-- Scenario 2: Clear a staging table before fresh import
TRUNCATE TABLE staging_import;
-- Use TRUNCATE: Remove all rows quickly, keep structure for next import
 
-- Scenario 3: Remove a table that's no longer needed
DROP TABLE legacy_customer_preferences;
-- Use DROP: Structure itself is obsolete
 
-- Scenario 4: Test cleanup—reset everything
TRUNCATE TABLE test_orders, test_customers, test_products RESTART IDENTITY CASCADE;
-- Use TRUNCATE: Fast, resets sequences, keeps tables for next test run

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98

-- ============================================
-- Pattern 1: Pre-Drop Verification Query
-- ============================================
-- Run this BEFORE every production DROP
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as size,
    n_live_tup as approximate_row_count
FROM pg_stat_user_tables
WHERE tablename = 'table_you_want_to_drop';
 
-- Output should match your expectations!
 
 
-- ============================================
-- Pattern 2: Archive Before Drop
-- ============================================
-- Create a timestamped archive table
CREATE TABLE archive_orders_20240115 AS 
SELECT * FROM orders WHERE status = 'completed';
 
-- Verify archive has expected data
SELECT COUNT(*) FROM archive_orders_20240115;
 
-- Only THEN drop (if needed)
-- DROP TABLE orders;  -- Uncomment when sure
 
 
-- ============================================
-- Pattern 3: Soft Deprecation (Rename, Don't Drop)
-- ============================================
-- Step 1: Rename table to indicate pending deletion
ALTER TABLE legacy_feature_data 
    RENAME TO legacy_feature_data_to_delete_20240315;
 
-- Step 2: Wait observation period (e.g., 2 weeks)
-- If anything breaks, it references the obviously-deprecated name
 
-- Step 3: After observation, actually drop
DROP TABLE legacy_feature_data_to_delete_20240315;
 
 
-- ============================================
-- Pattern 4: Transaction Wrapper with Confirm
-- ============================================
-- PostgreSQL: Execute DROP in transaction with explicit commit
BEGIN;
 
-- Show what we're about to drop
SELECT tablename, pg_size_pretty(pg_total_relation_size(tablename::regclass))
FROM pg_tables WHERE tablename = 'target_table';
 
-- Perform the drop
DROP TABLE target_table;
 
-- At this point, NOT YET committed!
-- Verify application still works (in another session)
-- If something's wrong: ROLLBACK;
-- If everything's fine: COMMIT;
 
-- COMMIT;
 
 
-- ============================================
-- Pattern 5: Export Data Before Drop
-- ============================================
-- PostgreSQL: Export to file before dropping
-- (Run from command line with psql)
-- \COPY (SELECT * FROM old_data) TO '/backup/old_data_export.csv' CSV HEADER;
 
-- Or use pg_dump for full table backup
-- pg_dump -t old_data database_name > old_data_backup.sql
 
-- Verify export file exists and has data
-- Then drop
DROP TABLE old_data;
 
 
-- ============================================
-- Pattern 6: Foreign Key Pre-Check
-- ============================================
-- Find all foreign keys pointing to this table
SELECT
    tc.table_schema || '.' || tc.table_name AS referencing_table,
    kcu.column_name AS referencing_column,
    ccu.table_name AS referenced_table,
    ccu.column_name AS referenced_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
    AND ccu.table_name = 'table_to_drop';
 
-- If results: Handle these FKs before dropping!
-- Options: DROP ... CASCADE, or drop FKs explicitly first

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91

-- ============================================
-- Dropping Multiple Tables: Order Matters
-- ============================================
 
-- Given these tables with foreign keys:
-- orders -> customers (orders.customer_id references customers.id)
-- order_items -> orders (order_items.order_id references orders.id)
-- order_items -> products (order_items.product_id references products.id)
 
-- WRONG ORDER: This fails due to foreign key constraints
DROP TABLE customers;        -- Fails: orders.customer_id references this
DROP TABLE orders;           -- Would fail: order_items.order_id references this
 
-- CORRECT ORDER: Drop children before parents
DROP TABLE order_items;      -- No dependencies on this table
DROP TABLE orders;           -- Now safe: order_items is gone
DROP TABLE customers;        -- Now safe: orders is gone
DROP TABLE products;         -- Now safe: order_items is gone
 
 
-- ============================================
-- Multi-Table Drop Syntax
-- ============================================
-- Drop multiple tables in one statement (order still matters)
DROP TABLE order_items, orders, customers, products;
 
-- With IF EXISTS for robustness
DROP TABLE IF EXISTS order_items, orders, customers, products;
 
 
-- ============================================
-- CASCADE: Automatic Dependency Resolution
-- ============================================
-- CASCADE removes FK constraints automatically, allowing any order
DROP TABLE customers CASCADE;  -- Drops FK constraints referencing customers
DROP TABLE products CASCADE;
 
-- Or all at once with CASCADE
DROP TABLE IF EXISTS customers, products, orders, order_items CASCADE;
 
 
-- ============================================
-- Finding Drop Order Automatically
-- ============================================
-- PostgreSQL: Query to find table dependency order
WITH RECURSIVE table_deps AS (
    -- Base case: Tables with no outgoing foreign keys
    SELECT 
        t.oid::regclass::text AS table_name,
        0 AS depth
    FROM pg_class t
    JOIN pg_namespace n ON t.relnamespace = n.oid
    WHERE n.nspname = 'public'
        AND t.relkind = 'r'
        AND NOT EXISTS (
            SELECT 1 FROM pg_constraint c 
            WHERE c.conrelid = t.oid AND c.contype = 'f'
        )
    
    UNION
    
    -- Recursive case: Tables that only reference already-listed tables
    SELECT 
        t.oid::regclass::text,
        td.depth + 1
    FROM pg_class t
    JOIN pg_namespace n ON t.relnamespace = n.oid
    JOIN pg_constraint c ON c.conrelid = t.oid AND c.contype = 'f'
    JOIN table_deps td ON c.confrelid::regclass::text = td.table_name
    WHERE n.nspname = 'public' AND t.relkind = 'r'
)
SELECT table_name, MAX(depth) as drop_order
FROM table_deps
GROUP BY table_name
ORDER BY drop_order DESC;  -- Drop in this order (highest first)
 
 
-- ============================================
-- Dropping All Tables in a Schema
-- ============================================
-- PostgreSQL: Generate DROP statements for all tables
SELECT 'DROP TABLE IF EXISTS ' || table_schema || '.' || table_name || ' CASCADE;'
FROM information_schema.tables
WHERE table_schema = 'public'
    AND table_type = 'BASE TABLE';
 
-- Execute the generated statements (be VERY careful with this!)
 
-- Or drop the entire schema (more nuclear)
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;  -- Recreate empty schema

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

-- ============================================
-- Recovery Option 1: ROLLBACK Transaction
-- ============================================
BEGIN;
DROP TABLE important_data;
-- Oops! Realized mistake immediately
 
ROLLBACK;  -- Table is restored!
 
-- Check it's back
SELECT COUNT(*) FROM important_data;  -- Works!
 
 
-- ============================================
-- Recovery Option 2: Restore from pg_dump backup
-- ============================================
-- Step 1: Create temporary database for restoration
-- createdb temp_restore
 
-- Step 2: Restore backup to temp database
-- pg_restore -d temp_restore backup_file.dump
 
-- Step 3: Copy table from temp to production
-- pg_dump -t recovered_table temp_restore | psql production_db
 
-- Or use dblink to copy directly
CREATE EXTENSION IF NOT EXISTS dblink;
 
INSERT INTO important_data
SELECT * FROM dblink(
    'dbname=temp_restore',
    'SELECT * FROM important_data'
) AS t(/* specify columns */);
 
 
-- ============================================
-- Recovery Option 3: WAL-based Point-in-Time Recovery
-- ============================================
-- This requires prior configuration of:
-- - archive_mode = on
-- - archive_command = 'cp %p /archive/%f'
-- - Proper backup_label from pg_basebackup
 
-- Recovery process (outline):
-- 1. Stop the database
-- 2. Clear data directory (save the corrupted version first!)
-- 3. Restore base backup
-- 4. Copy WAL files to pg_wal
-- 5. Create recovery.signal (PostgreSQL 12+) or recovery.conf
-- 6. Set recovery_target_time = '2024-01-15 14:30:00'
-- 7. Start database—it replays WAL up to target time
 
-- postgresql.conf for recovery:
-- restore_command = 'cp /archive/%f %p'
-- recovery_target_time = '2024-01-15 14:30:00'
-- recovery_target_action = 'promote'
 
 
-- ============================================
-- Preventive: Create Table Recovery Log
-- ============================================
-- Log all table drops for potential recovery reference
CREATE TABLE drop_log (
    id              SERIAL PRIMARY KEY,
    table_name      VARCHAR(200) NOT NULL,
    schema_name     VARCHAR(200) NOT NULL,
    dropped_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    dropped_by      VARCHAR(100) DEFAULT CURRENT_USER,
    row_count       BIGINT,
    table_size      TEXT,
    reason          TEXT
);
 
-- Before any intentional drop, log it:
INSERT INTO drop_log (table_name, schema_name, row_count, table_size, reason)
SELECT 
    'old_audit_log', 
    'public',
    COUNT(*),
    pg_size_pretty(pg_total_relation_size('old_audit_log')),
    'Migrated to new audit system per ticket JIRA-1234'
FROM old_audit_log;