Kenhuang Academy
  • English

      • 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

      OptionDescription
      CASCADEAutomatically drops dependent objects (views, foreign keys, etc.)
      RESTRICTRefuses 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

      FeatureDROP TABLETRUNCATE
      Deletes dataYesYes
      Deletes structureYesNo
      Deletes indexesYesNo
      RollbackNoYes (in transaction)
      Fires triggersNoConfigurable
      Resets sequencesTable is deletedOptional

      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;