Drop & Truncate Tables

Sometimes you need to remove a table entirely or wipe all its data clean and start fresh. PostgreSQL gives you two commands for this — DROP TABLE and TRUNCATE. They sound similar but serve very different purposes. Understanding exactly what each one does — and what it does to related tables — is critical before you use either one in a real database. Both are instant and permanent with no undo option, so this lesson makes sure you know precisely what you are doing before you pull the trigger.

DROP TABLE — Removing a Table Completely

DROP TABLE deletes a table entirely — the structure, all the data, all its indexes, constraints, and triggers disappear permanently. It is as if the table never existed. This is the right command when a table is no longer needed at all — not just its data, but the table itself.

-- Create a test table to practice dropping
CREATE TABLE temp_logs (
  id         SERIAL PRIMARY KEY,
  message    TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO temp_logs (message) VALUES ('System started'), ('User logged in');

-- Drop the table — everything gone permanently
DROP TABLE temp_logs;

-- Verify it is gone
\dt

DROP TABLE IF EXISTS

If you try to drop a table that does not exist, PostgreSQL throws an error and stops your script. Adding IF EXISTS makes the command safe — PostgreSQL simply skips it silently if the table is not there. This is essential in setup and teardown scripts that might run multiple times.

-- Without IF EXISTS — errors if table does not exist
DROP TABLE temp_logs;  -- ERROR if temp_logs does not exist

-- With IF EXISTS — safe, no error
DROP TABLE IF EXISTS temp_logs;

-- Drop multiple tables in one statement
DROP TABLE IF EXISTS temp_logs, staging_data, old_reports;

DROP TABLE and Foreign Keys — The CASCADE Problem

This is where things get important. If other tables have foreign keys pointing to the table you want to drop, PostgreSQL will refuse to drop it. This protection exists to prevent your database from ending up with orphaned rows — orders pointing to a customer table that no longer exists, for example.

You have two choices. Either drop the dependent tables first in the correct order, or use CASCADE which tells PostgreSQL to automatically drop all dependent tables and objects along with the target table. CASCADE is powerful but dangerous — it can remove far more than you intended if many tables are linked together.

-- Try to drop customers — fails because orders references it
DROP TABLE customers;

-- Option 1: drop in the right order manually (safest)
DROP TABLE order_items;  -- drop child first
DROP TABLE orders;       -- then parent's child
DROP TABLE customers;    -- now safe to drop

-- Option 2: CASCADE — drops customers AND everything that depends on it
DROP TABLE customers CASCADE;

-- What CASCADE removes:
-- • The customers table itself
-- • Any foreign key constraints in other tables pointing to customers
-- • Any views built on customers
-- • Any indexes on customers

DROP Multiple Tables

-- Drop multiple tables at once — order matters if foreign keys exist
DROP TABLE IF EXISTS order_items, orders, customers, products, employees;

-- With CASCADE to handle any remaining dependencies
DROP TABLE IF EXISTS order_items, orders, customers, products, employees CASCADE;

TRUNCATE — Wiping All Data Without Removing the Table

TRUNCATE removes every row from a table instantly — but the table structure stays completely intact. The columns, constraints, indexes, and defaults all remain. It is like emptying a container without destroying it. Use TRUNCATE when you want to clear data and reuse the table — for example, clearing a staging table before loading fresh data, resetting test data between test runs, or emptying a cache table.

-- Create a staging table
CREATE TABLE staging_orders (
  id          SERIAL PRIMARY KEY,
  customer_id INTEGER,
  total       NUMERIC(10,2)
);

INSERT INTO staging_orders (customer_id, total) VALUES (1, 150.00), (2, 320.00), (3, 89.99);

SELECT COUNT(*) FROM staging_orders;  -- shows 3

-- Wipe all rows instantly — table structure remains
TRUNCATE TABLE staging_orders;

SELECT COUNT(*) FROM staging_orders;  -- shows 0

-- The table is still there — just empty
\d staging_orders

TRUNCATE and the SERIAL Sequence

When you TRUNCATE a table that has a SERIAL column, the auto-increment counter does not reset by default — the next inserted row continues from where the sequence left off. If you want the ID counter to restart from 1, add RESTART IDENTITY.

INSERT INTO staging_orders (customer_id, total) VALUES (10, 500.00);
SELECT id FROM staging_orders;   -- id might be 4 not 1 — sequence continued

-- Truncate AND reset the ID sequence back to 1
TRUNCATE TABLE staging_orders RESTART IDENTITY;

INSERT INTO staging_orders (customer_id, total) VALUES (10, 500.00);
SELECT id FROM staging_orders;   -- now id = 1

TRUNCATE with Foreign Keys — CASCADE

Just like DROP, TRUNCATE is blocked when other tables have foreign keys pointing to the table you are truncating. PostgreSQL refuses to clear rows that child tables still reference. Use CASCADE to truncate the table and all tables that reference it at the same time.

-- Truncate a parent table — blocked by foreign keys
TRUNCATE TABLE orders;  -- fails if order_items references orders

-- CASCADE truncates orders AND order_items together
TRUNCATE TABLE orders CASCADE;

-- Truncate multiple tables at once
TRUNCATE TABLE orders, order_items RESTART IDENTITY;

-- Truncate all five course tables and reset all IDs
TRUNCATE TABLE order_items, orders, products, customers, employees RESTART IDENTITY;

TRUNCATE vs DELETE — A Critical Difference

There is another way to remove all rows from a table — DELETE FROM table_name with no WHERE clause. It looks similar to TRUNCATE but works very differently. Knowing which one to use matters a lot in production databases.

DROP vs TRUNCATE — Summary

Next up: Identity columns and sequences — how PostgreSQL generates unique numbers automatically.