All posts
PostgreSQLNovember 2, 20267 min read

PostgreSQL DROP TABLE vs TRUNCATE vs DELETE Explained

PostgreSQL DROP TABLE vs TRUNCATE vs DELETE explained for beginners. Learn what each command removes, when to use it, and how to undo mistakes safely.

F
Fepiq Team
Fepiq

What is the difference between DROP TABLE, TRUNCATE and DELETE in PostgreSQL? In short: DELETE removes some or all rows from a table, TRUNCATE removes all rows very quickly but keeps the table, and DROP TABLE removes the whole table, rows and structure together. This guide explains each one in plain words, with small examples you can copy and run.

You do not need any experience. We will define each word the first time we use it. If you have not made a table yet, read our guide on how to create a table in PostgreSQL first.

The quick answer

CommandWhat it removesKeeps the table?Can you use WHERE?
DELETEChosen rows (or all rows)YesYes
TRUNCATEAll rows at onceYesNo
DROP TABLEThe whole table and its rowsNoNo

Think of a table as a notebook. DELETE tears out some pages. TRUNCATE tears out every page but keeps the empty notebook. DROP TABLE throws the whole notebook away.

Set up a practice table

A table is a grid that stores data in rows (records) and columns (fields). Open psql, the PostgreSQL command-line tool, and run this to create a small table with three rows.

sql
CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  grade INTEGER
);

INSERT INTO students (name, grade)
VALUES ('Ana', 80), ('Ben', 55), ('Chen', 92);

The first statement creates a table called students. The id column counts up by itself (SERIAL) and is the primary key, a unique label for each row. The INSERT statement adds three students. Each row gets id 1, 2 and 3.

DELETE: remove specific rows

DELETE is the safest choice when you only want to remove some rows. You choose them with a WHERE clause, which is a filter.

sql
DELETE FROM students
WHERE grade < 60;

DELETE FROM students says which table to change. WHERE grade < 60 limits the action to rows where grade is below 60. Only Ben is removed. Ana and Chen stay.

Be careful: if you leave out WHERE, DELETE removes every row. PostgreSQL will not stop you.

sql
DELETE FROM students;

This empties the table. The table still exists, but it has zero rows. You can undo this only if you ran it inside a transaction (see below).

TRUNCATE: empty the table fast

TRUNCATE removes all rows in one step. It is much faster than DELETE on big tables because it does not check each row one by one. It has no WHERE, so it is all or nothing.

sql
TRUNCATE TABLE students;

This removes every row from students and keeps the table and its columns. Afterwards, SELECT * FROM students returns nothing.

Reset the ID counter

After DELETE, the next new row keeps counting from where it stopped. If the last id was 3, the next one is 4. If you want to start again from 1, use TRUNCATE with a special option.

sql
TRUNCATE TABLE students RESTART IDENTITY;

RESTART IDENTITY sets the auto-counting id back to 1. This is useful on practice or test tables. Do not use it on real data unless you are sure.

DROP TABLE: delete the whole table

DROP TABLE removes the table itself. The rows, the columns and the table name are all gone.

sql
DROP TABLE students;

After this, any query on students gives an error such as 'relation students does not exist'. To avoid that error when the table might be missing, add IF EXISTS.

sql
DROP TABLE IF EXISTS students;

IF EXISTS means: drop it if it is there, and do nothing (no error) if it is not.

What if other tables depend on it?

Tables can be linked with foreign keys, which point from one table to another. PostgreSQL will refuse to drop or truncate a table that others depend on.

sql
TRUNCATE TABLE students CASCADE;
DROP TABLE students CASCADE;

CASCADE also applies the action to the dependent tables. This can wipe more data than you expect, so read the error message and think before you add it.

Undo mistakes with a transaction

A transaction groups commands so you can cancel them. In PostgreSQL, even TRUNCATE and DROP TABLE can be undone inside a transaction, which is a handy safety net.

sql
BEGIN;
DELETE FROM students;
SELECT COUNT(*) FROM students;
ROLLBACK;

BEGIN starts the transaction. DELETE removes the rows. COUNT shows 0, so you can check your work. ROLLBACK cancels everything and brings the rows back. Use COMMIT instead if you are happy with the result.

Which one should you use?

  • Remove some rows: use DELETE with WHERE.
  • Clear a whole table but keep it: use TRUNCATE.
  • Remove the table forever: use DROP TABLE.
  • Not sure? Use BEGIN and ROLLBACK first, and back up important data.

Common beginner mistakes

  • Running DELETE without WHERE and emptying the table.
  • Mixing up TRUNCATE and DROP. TRUNCATE keeps the table; DROP does not.
  • Using CASCADE without knowing which tables it affects.
  • Practising on a real database instead of a test one.

Frequently asked questions

What is the difference between DELETE and TRUNCATE in PostgreSQL?+

DELETE can remove chosen rows using WHERE and works row by row. TRUNCATE removes all rows at once, is faster on large tables, and cannot use WHERE. Both keep the table itself.

What is the difference between TRUNCATE and DROP TABLE?+

TRUNCATE empties the table but keeps its structure, so you can insert new rows right away. DROP TABLE deletes the table completely, including its columns, so you must create it again to use it.

Can I undo DROP TABLE or TRUNCATE in PostgreSQL?+

Only if you ran it inside a transaction that you have not committed yet. Use ROLLBACK to undo it. After COMMIT, you need a backup to get the data back.

Does TRUNCATE reset the auto-increment id?+

Not by default. Add RESTART IDENTITY to start the id counter again from 1. DELETE never resets the counter.

Why does PostgreSQL say it cannot truncate a table?+

Another table has a foreign key pointing to it. Empty or drop the dependent table first, or use CASCADE if you are sure you want to clear both.

Want a deeper look at changing and removing rows? Read our guide to updating and deleting data in PostgreSQL.

Read the UPDATE and DELETE guide

Need help turning your database skills into a real app? Talk to the Fepiq team.

Contact Fepiq

Get new posts in your inbox

Occasional, no-fluff notes on shipping modern software — startups, automation, Laravel, Shopify and more. No spam, unsubscribe anytime.

Keep reading

Related posts

All posts

Let's build something

Ready to ship your next product with Fepiq?

Book a free discovery call. We'll listen, ask sharp questions, and send you a proposal within 3 business days.