DROP TABLE and TRUNCATE TABLE

Objectives

By the end of this lesson, you should be able to:

• Remove a table entirely with DROP TABLE
• Empty a table’s data while keeping its structure with TRUNCATE TABLE
• Explain when each is the right choice

💡 Why this matters: Sometimes a table needs to disappear completely, sometimes it just needs to be emptied out and reused. These are two different operations, with two different levels of permanence.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database.

DROP TABLE: Removing a Table Entirely

DROP TABLE employees;

This deletes the table itself, structure and all data, permanently. There’s no undo. Dropping a table that doesn’t exist raises an error:

DROP TABLE nonexistent_table;

ERROR: table "nonexistent_table" does not exist

DROP TABLE IF EXISTS avoids that error when a table might not be there:

DROP TABLE IF EXISTS nonexistent_table;

This succeeds silently whether or not the table existed.

TRUNCATE TABLE: Emptying a Table, Keeping Its Structure

TRUNCATE TABLE scratch_emp;

This removes every row from the table, but the table itself, its columns, types, and constraints, stays exactly as it was:

before TRUNCATE: [{"id":1,"name":"Erin"}, {"id":2,"name":"Jordan"}, {"id":3,"name":"Maya"}]
after TRUNCATE: []

The table is ready to insert into again immediately, no CREATE TABLE needed. By default, a SERIAL or IDENTITY column’s counter is not reset, the next insert continues from where it left off, not back at 1:

INSERT INTO scratch_emp (name) VALUES ('Priya');

 id | name
----+-------
  4 | Priya

To reset the counter back to 1 as well, add RESTART IDENTITY:

TRUNCATE TABLE scratch_emp RESTART IDENTITY;
INSERT INTO scratch_emp (name) VALUES ('Sam');

 id | name
----+------
  1 | Sam

Choosing Between Them

Use DROP TABLE when the table itself shouldn’t exist anymore, a mistake, an obsolete design. Use TRUNCATE TABLE when you want to keep the table’s structure but clear out all its data, resetting a test environment, for example, without redefining every column and constraint from scratch.

Try It

1. Create a small table, insert a few rows, then TRUNCATE it and confirm the rows are gone but the table still exists.
2. Insert a new row after truncating, and check whether its id continues from before or restarts, based on whether you used RESTART IDENTITY.
3. Write a DROP TABLE IF EXISTS statement for a table that doesn’t exist, and confirm it doesn’t raise an error.

Recap

• DROP TABLE permanently removes a table, structure and data both, DROP TABLE IF EXISTS avoids an error if it might not exist.
• TRUNCATE TABLE removes all rows but keeps the table’s structure intact, ready to use again immediately.
• TRUNCATE ... RESTART IDENTITY also resets a SERIAL/IDENTITY column’s counter back to its starting value.

Next lesson: this module’s exercises, practicing everything from CREATE TABLE through dropping and truncating one.