DROP TABLE & TRUNCATE

Sometimes you need to remove data or entire tables. PostgreSQL provides two different tools for this: DROP TABLE removes the table entirely (structure and data), while TRUNCATE removes all rows but keeps the empty table ready for new data. Understanding the difference -- and the risks -- is essential.

DROP TABLE

DROP TABLE completely removes a table from the database. The table definition, all its data, indexes, constraints, triggers, and rules are all permanently deleted.

Basic Syntax

DROP TABLE table_name;

Example:

DROP TABLE temp_reports;

After this command, the temp_reports table no longer exists. Any query that references it will fail.

IF EXISTS

If you try to drop a table that does not exist, PostgreSQL raises an error:

DROP TABLE nonexistent_table;
-- ERROR: table "nonexistent_table" does not exist

Use IF EXISTS to silently do nothing if the table is not there:

DROP TABLE IF EXISTS temp_reports;

This is especially useful in scripts and migrations where you want to ensure a clean state.

Dropping Multiple Tables

You can drop several tables in one command:

DROP TABLE IF EXISTS temp_users, temp_orders, temp_logs;

This is more efficient than three separate DROP TABLE statements.

CASCADE and RESTRICT

Tables can have dependencies. For example, another table's foreign key might reference this table, or a view might query it.

RESTRICT (the default) prevents dropping if dependencies exist:

DROP TABLE departments;
-- ERROR: cannot drop table "departments" because other objects depend on it
-- DETAIL: constraint fk_department on table "employees" depends on it

CASCADE automatically drops all dependent objects:

DROP TABLE departments CASCADE;
-- This also drops:
--   - Foreign key constraints in other tables that reference departments
--   - Views that query the departments table
--   - Any other dependent objects

Warning: CASCADE can have far-reaching effects. It might drop views, constraints, and functions you did not realize were connected. Always check dependencies before using CASCADE in production:

-- Check what depends on a table before dropping
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_class AS source_table ON pg_depend.refobjid = source_table.oid
JOIN pg_namespace AS dependent_ns ON dependent_view.relnamespace = dependent_ns.oid
WHERE source_table.relname = 'your_table_name';

TRUNCATE TABLE

TRUNCATE removes all rows from a table but preserves the table structure (columns, constraints, indexes):

Basic Syntax

TRUNCATE TABLE employees;

After this, the employees table is empty but still exists. You can immediately insert new rows.

The TABLE keyword is optional:

TRUNCATE employees;

TRUNCATE vs. DELETE

Both can remove all rows, but they work very differently:

-- These produce the same visible result:
DELETE FROM employees;
TRUNCATE TABLE employees;

When to use DELETE: When you need to remove specific rows (WHERE clause), when row-level triggers must fire, or when you need fine-grained control.

When to use TRUNCATE: When you want to remove ALL rows and speed matters, such as clearing staging tables, resetting test data, or rebuilding data from scratch.

RESTART IDENTITY vs. CONTINUE IDENTITY

If a table has a SERIAL column, TRUNCATE does not reset the sequence by default:

-- After inserting rows 1-100, then truncating:
TRUNCATE TABLE users;
-- Next insert gets id 101, not 1

-- To reset the counter back to 1:
TRUNCATE TABLE users RESTART IDENTITY;
-- Next insert gets id 1

The default behavior is CONTINUE IDENTITY, which keeps the sequence at its current value.

CASCADE with TRUNCATE

Like DROP TABLE, TRUNCATE can cascade to related tables:

TRUNCATE TABLE departments CASCADE;

This truncates departments and also truncates any table that has a foreign key referencing departments. This prevents foreign key violations.

Without CASCADE, truncating a table that is referenced by foreign keys will fail:

TRUNCATE TABLE departments;
-- ERROR: cannot truncate a table referenced in a foreign key constraint

Truncating Multiple Tables

TRUNCATE TABLE orders, order_items, payments RESTART IDENTITY;

This clears all three tables in one atomic operation and resets their sequences.

Practical Scenarios

Scenario 1: Resetting a Test Database

-- Clear all test data and reset IDs
TRUNCATE TABLE
    orders,
    order_items,
    customers,
    products
RESTART IDENTITY CASCADE;

Scenario 2: Removing a Feature

When a feature is retired and its tables are no longer needed:

-- First, check for dependencies
-- Then remove the tables
DROP TABLE IF EXISTS feature_flags CASCADE;
DROP TABLE IF EXISTS feature_settings CASCADE;
DROP TABLE IF EXISTS feature_logs CASCADE;

Scenario 3: Rebuilding a Summary Table

-- Clear and rebuild a reporting table daily
TRUNCATE TABLE daily_sales_summary;

INSERT INTO daily_sales_summary (date, total_sales, order_count)
SELECT
    order_date,
    SUM(total_price),
    COUNT(*)
FROM orders
GROUP BY order_date;

Scenario 4: Safe Migration Script

-- Create a new table, move data, drop the old one
CREATE TABLE users_v2 (
    id SERIAL PRIMARY KEY,
    full_name TEXT NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO users_v2 (full_name, email)
SELECT name, email FROM users;

DROP TABLE users CASCADE;

ALTER TABLE users_v2 RENAME TO users;

Safety Checklist

Before running DROP TABLE or TRUNCATE in production:

1. Check dependencies: Are there foreign keys, views, or functions that reference this table?
2. Verify backups: Do you have a recent backup if you need to recover the data?
3. Consider timing: Is this table currently being used by running queries or applications?
4. Test first: Run the command in a development or staging environment first.
5. Use transactions: Wrap destructive operations in a transaction so you can roll back if something goes wrong:

BEGIN;

TRUNCATE TABLE staging_data RESTART IDENTITY;
-- Verify the result
-- If something is wrong: ROLLBACK;
-- If everything is OK:
COMMIT;

Try It Yourself

Think through these scenarios:

1. You have three temporary tables (temp_a, temp_b, temp_c) that may or may not exist. Write a statement to drop all of them safely.

DROP TABLE IF EXISTS temp_a, temp_b, temp_c;

2. You want to clear all data from a logs table that has a SERIAL id column, and you want the next log entry to start at id 1. Write the statement.

TRUNCATE TABLE logs RESTART IDENTITY;

3. You need to drop a categories table, but the products table has a foreign key referencing it. What are your two options, and what is the risk of each?

Option A -- Remove the foreign key first, then drop:

ALTER TABLE products DROP CONSTRAINT fk_category;
DROP TABLE categories;

Risk: The products table can now contain invalid category_id values with no referential integrity.

Option B -- Use CASCADE:

DROP TABLE categories CASCADE;

Risk: This silently drops the foreign key constraint (and any views referencing categories), which might break application logic that depends on those objects.