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.
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.
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.
| Command | What it removes | Keeps the table? | Can you use WHERE? |
|---|---|---|---|
| DELETE | Chosen rows (or all rows) | Yes | Yes |
| TRUNCATE | All rows at once | Yes | No |
| DROP TABLE | The whole table and its rows | No | No |
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.
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.
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 is the safest choice when you only want to remove some rows. You choose them with a WHERE clause, which is a filter.
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.
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 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.
TRUNCATE TABLE students;This removes every row from students and keeps the table and its columns. Afterwards, SELECT * FROM students returns nothing.
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.
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 removes the table itself. The rows, the columns and the table name are all gone.
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.
DROP TABLE IF EXISTS students;IF EXISTS means: drop it if it is there, and do nothing (no error) if it is not.
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.
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.
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.
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.
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.
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.
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.
Not by default. Add RESTART IDENTITY to start the id counter again from 1. DELETE never resets the counter.
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 guideNeed help turning your database skills into a real app? Talk to the Fepiq team.
Contact FepiqOccasional, no-fluff notes on shipping modern software — startups, automation, Laravel, Shopify and more. No spam, unsubscribe anytime.
Keep reading
Learn SQL AND, OR and NOT operators with simple examples. See how to combine conditions in WHERE and avoid the common mistakes beginners make.
Learn JavaScript template literals with simple examples: backticks, ${} variables, and multi-line strings. A beginner-friendly guide you can copy and run.
Let's build something
Book a free discovery call. We'll listen, ask sharp questions, and send you a proposal within 3 business days.