When working with PostgreSQL, you’ll often need to remove data from a table. PostgreSQL provides several commands for this, but DELETE, TRUNCATE, and DROP are not interchangeable.

The three commands have very different purposes:

DELETE
→ Removes selected rows

TRUNCATE
→ Removes all rows quickly

DROP
→ Removes the table itself

Choosing the wrong command can lead to accidentally deleting more data or even removing an entire database object.

In this guide, we’ll compare PostgreSQL DELETE vs TRUNCATE vs DROP, including syntax, performance, transactions, identity sequences, foreign keys, triggers, and practical use cases.

---

DELETE vs TRUNCATE vs DROP at a Glance

Feature	DELETE	TRUNCATE	DROP
Removes rows	Yes	Yes	Yes
Removes table structure	No	No	Yes
Can remove selected rows	Yes	No	No
Supports WHERE	Yes	No	No
Generally fastest for all rows	No	Yes	Yes, because object is removed
Can be rolled back	Yes	Yes	Yes
Fires row-level DELETE triggers	Yes	No	No
Identity reset	No by default	No by default	Table is removed
Supports RETURNING	Yes	No	No
Keeps table	Yes	Yes	No
Best for	Selected rows	Emptying a table	Removing the object

---

What Is DELETE?

DELETE removes rows from a table.

The basic syntax is:

DELETE FROM table_name;

This removes every row from the table while keeping the table itself.

For example:

DELETE FROM users;

The table remains:

users
├── id
├── name
└── email

but its rows are removed.

---

DELETE With WHERE

The biggest advantage of DELETE is that you can remove specific rows.

For example:

DELETE FROM users
WHERE id = 10;

Only the user with ID 10 is removed.

You can also delete multiple rows:

DELETE FROM users
WHERE is_active = FALSE;

This removes all inactive users.

---

DELETE All Rows

You can remove all rows using:

DELETE FROM users;

However, if the goal is simply to empty a large table, TRUNCATE is often more appropriate.

Why?

Because DELETE processes the rows as row deletions, while TRUNCATE is designed specifically for quickly removing all rows.

---

DELETE With RETURNING

PostgreSQL supports RETURNING with DELETE.

For example:

DELETE FROM users
WHERE id = 10
RETURNING *;

This returns the row that was deleted.

You can return specific columns:

DELETE FROM users
WHERE id = 10
RETURNING id, email;

This is particularly useful in applications because you may not need a separate SELECT before or after the deletion.

---

DELETE and Transactions

DELETE works with PostgreSQL transactions.

For example:

BEGIN;

DELETE FROM users
WHERE id = 10;

ROLLBACK;

The deleted row is restored because the transaction was rolled back.

You can also commit the operation:

BEGIN;

DELETE FROM users
WHERE id = 10;

COMMIT;

Now the deletion becomes permanent as part of the committed transaction.

---

What Is TRUNCATE?

TRUNCATE removes all rows from a table without removing the table itself.

Example:

TRUNCATE TABLE users;

Afterward:

users table
    ↓
still exists
    ↓
0 rows

The table structure, columns, indexes, and other table-level properties remain.

---

TRUNCATE Is Designed for Emptying Tables

If you need to remove every row from a large table, TRUNCATE is generally much faster than:

DELETE FROM users;

because PostgreSQL can remove the table’s stored row data in a more direct way rather than processing each row individually.

This makes TRUNCATE useful for:

• Temporary data
• Staging tables
• Test data
• Import pipelines
• Development databases
• Resetting tables

---

TRUNCATE Does Not Support WHERE

You cannot write:

TRUNCATE TABLE users
WHERE id = 10;

This is invalid.

TRUNCATE always operates on the entire table.

If you need selective deletion:

DELETE FROM users
WHERE id = 10;

is the correct command.

---

TRUNCATE Multiple Tables

PostgreSQL allows multiple tables:

TRUNCATE TABLE users, orders, products;

All specified tables are truncated.

You should be careful when doing this because the operation removes all rows from every listed table.

---

TRUNCATE and Identity Sequences

Consider:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT
);

After inserting rows, IDs might be:

1
2
3
4
5

If you run:

TRUNCATE TABLE users;

the identity sequence is not reset by default.

To reset identity values, use:

TRUNCATE TABLE users
RESTART IDENTITY;

Afterward, a new row can start again from the identity sequence’s initial value.

---

TRUNCATE CONTINUE IDENTITY

The default behavior can be made explicit with:

TRUNCATE TABLE users
CONTINUE IDENTITY;

This keeps the associated identity sequence state.

For most applications, simply using:

TRUNCATE TABLE users;

is enough if you don’t want to reset identity values.

---

TRUNCATE RESTART IDENTITY

If you want to clear the table and reset identity sequences:

TRUNCATE TABLE users
RESTART IDENTITY;

This is especially useful for test or development data.

---

TRUNCATE CASCADE

Suppose:

users
  ↓
orders
  ↓
order_items

and foreign keys connect these tables.

PostgreSQL may prevent truncating a referenced table unless related tables are handled appropriately.

You can use:

TRUNCATE TABLE users
CASCADE;

This tells PostgreSQL to truncate tables that depend on the specified table through foreign-key relationships.

Use CASCADE carefully because it can affect more tables than you initially intended.

---

TRUNCATE and Foreign Keys

TRUNCATE has stricter behavior around foreign-key relationships than ordinary DELETE.

For example:

users
   ↓
orders

If orders.user_id references users.id, PostgreSQL may require you to truncate the related tables together or use an appropriate CASCADE.

This is one reason you should understand your table relationships before using:

TRUNCATE ... CASCADE;

---

What Is DROP?

DROP removes a database object.

For a table:

DROP TABLE users;

This doesn’t just remove the rows.

It removes the table itself.

After:

DROP TABLE users;

the following no longer exists:

users
├── columns
├── rows
├── indexes
├── constraints
└── table definition

The object is gone.

---

DROP TABLE Example

Before:

users
├── id
├── name
└── email

Run:

DROP TABLE users;

After:

users
→ Does not exist

Trying:

SELECT *
FROM users;

will produce an error because the table has been removed.

---

DROP TABLE CASCADE

If other objects depend on a table, PostgreSQL can prevent the drop.

For example:

DROP TABLE users;

may fail if other objects depend on users.

You can use:

DROP TABLE users CASCADE;

This removes the table and dependent objects.

Again, use CASCADE carefully.

It can remove more database objects than the command’s table name alone might suggest.

---

DROP vs TRUNCATE

This is one of the most important differences.

TRUNCATE

TRUNCATE TABLE users;

Result:

Table → Exists
Rows  → Removed

DROP

DROP TABLE users;

Result:

Table → Removed
Rows  → Removed

So:

TRUNCATE empties a table, while DROP removes the table itself.

---

DELETE vs TRUNCATE

Suppose your table contains:

1
2
3
4
5

DELETE

DELETE FROM users;

Result:

Table → Exists
Rows  → Removed

TRUNCATE

TRUNCATE TABLE users;

Result:

Table → Exists
Rows  → Removed

The final data state looks similar, but the operations behave differently internally and have different trigger, identity, and locking characteristics.

---

DELETE vs TRUNCATE Performance

For removing a small number of rows:

DELETE FROM users
WHERE id = 10;

is appropriate.

For removing all rows from a large table:

TRUNCATE TABLE users;

is generally much faster.

The reason is that TRUNCATE is designed as a bulk table operation rather than a row-by-row deletion.

---

DELETE vs TRUNCATE: Simple Rule

Use:

DELETE
→ I need to remove specific rows.

Use:

TRUNCATE
→ I need to remove all rows but keep the table.

This simple distinction handles most everyday cases.

---

DELETE vs DROP

Use:

DELETE FROM users;

when:

I want the table
+
I want to remove its rows

Use:

DROP TABLE users;

when:

I don't need the table anymore

For example, during development you may remove an obsolete table:

DROP TABLE old_logs;

---

TRUNCATE vs DROP

Use:

TRUNCATE TABLE logs;

when:

The table will be reused

Use:

DROP TABLE logs;

when:

The table itself is no longer needed

---

DELETE, TRUNCATE, and DROP Transactions

A common misconception is that TRUNCATE or DROP cannot be rolled back.

PostgreSQL supports transactional behavior for these commands.

For example:

BEGIN;

TRUNCATE TABLE users;

ROLLBACK;

The truncation can be rolled back when executed inside a transaction and when no other transaction behavior prevents the rollback.

Likewise:

BEGIN;

DROP TABLE users;

ROLLBACK;

can restore the dropped table as part of the transaction.

This is one of PostgreSQL’s useful transactional features.

---

DELETE and Triggers

DELETE fires applicable row-level DELETE triggers.

For example:

CREATE TRIGGER user_delete_trigger
AFTER DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_deletion();

Then:

DELETE FROM users
WHERE id = 10;

can invoke the trigger for the deleted row.

This makes DELETE appropriate when application-specific deletion logic needs to run.

---

TRUNCATE and Triggers

TRUNCATE does not fire row-level DELETE triggers because it does not delete rows individually.

PostgreSQL does support ON TRUNCATE triggers.

For example:

CREATE TRIGGER users_truncate_trigger
AFTER TRUNCATE ON users
FOR EACH STATEMENT
EXECUTE FUNCTION log_truncate();

This is different from ordinary row-level delete triggers.

---

DROP and Triggers

DROP TABLE removes the table and its associated triggers.

You should not expect row-level deletion logic to execute as though each row had been deleted.

If your application requires per-row deletion behavior, DROP is obviously the wrong operation.

---

DELETE and Foreign Keys

DELETE respects foreign-key constraints.

Suppose:

users
  ↓
orders

If an order references a user, PostgreSQL may prevent:

DELETE FROM users
WHERE id = 10;

unless the foreign-key relationship allows the deletion, for example through an appropriate ON DELETE CASCADE.

---

ON DELETE CASCADE

You can define:

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL
        REFERENCES users(id)
        ON DELETE CASCADE
);

Now:

DELETE FROM users
WHERE id = 10;

can automatically delete the related orders.

This is powerful but should be designed carefully.

---

TRUNCATE and Foreign Keys

TRUNCATE doesn’t use normal row-by-row foreign-key checks in the same way as DELETE.

PostgreSQL still protects referential integrity by requiring referenced tables to be included appropriately or by using:

TRUNCATE ... CASCADE;

This is one reason truncating interconnected tables requires extra caution.

---

DROP and Dependencies

DROP TABLE considers object dependencies.

For example:

users
 ↓
orders
 ↓
order_items

Dropping users may fail if dependent objects remain.

You can explicitly remove dependent objects with:

DROP TABLE users CASCADE;

But this should be treated as a potentially destructive schema operation.

---

DELETE Example: Remove Old Records

Suppose you have logs:

CREATE TABLE logs (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMPTZ NOT NULL
);

Remove logs older than 90 days:

DELETE FROM logs
WHERE created_at < NOW() - INTERVAL '90 days';

This is a perfect use case for DELETE because you’re removing only matching rows.

---

TRUNCATE Example: Reset Test Data

Suppose you’re testing an application and want to remove all test users:

TRUNCATE TABLE users
RESTART IDENTITY;

Now:

Rows → Removed
Identity → Reset
Table → Preserved

This is often useful when resetting test data.

---

DROP Example: Remove an Old Table

Suppose you no longer need:

legacy_users

You can run:

DROP TABLE legacy_users;

The table definition itself is removed.

---

TRUNCATE for Staging Tables

Data pipelines often use staging tables:

External data
      ↓
Staging table
      ↓
Transform
      ↓
Production tables

After processing, you may want to clear the staging table:

TRUNCATE TABLE staging_orders;

The table remains available for the next import.

---

DELETE for Selective Cleanup

Suppose a table contains temporary records:

temporary
permanent
temporary
permanent

You can selectively remove temporary records:

DELETE FROM records
WHERE type = 'temporary';

TRUNCATE cannot do this because it always removes all rows.

---

DROP for Schema Cleanup

During development, you may create temporary tables:

CREATE TABLE experiment_data (...);

Once the experiment is finished:

DROP TABLE experiment_data;

This removes the object completely.

---

Which One Is Safest?

It depends on what you want to preserve.

DELETE

Safest when you need selective row removal:

DELETE
→ Specific rows

TRUNCATE

Safe when you’re certain you want every row removed:

TRUNCATE
→ All rows

DROP

Most destructive to the schema object:

DROP
→ Entire table

Always verify the target before running destructive SQL in production.

---

DELETE vs TRUNCATE vs DROP: Decision Tree

Do you need to remove the table itself?
          │
        Yes
          ↓
         DROP


        No
          ↓
Do you need to remove only some rows?
       /        \
     Yes         No
      ↓           ↓
   DELETE      TRUNCATE

This is the simplest way to choose between the three commands.

---

Common Mistakes

Mistake 1: Using DELETE When You Need to Empty a Huge Table

Instead of:

DELETE FROM logs;

consider:

TRUNCATE TABLE logs;

if you truly need every row removed and its semantics are appropriate.

---

Mistake 2: Using TRUNCATE When You Need a WHERE Clause

This won’t work:

TRUNCATE TABLE users
WHERE is_active = FALSE;

Use:

DELETE FROM users
WHERE is_active = FALSE;

---

Mistake 3: Using DROP Instead of TRUNCATE

This:

DROP TABLE users;

doesn’t just clear the data.

It removes the table.

If you need to reuse the table:

TRUNCATE TABLE users;

is the appropriate command.

---

Mistake 4: Forgetting RESTART IDENTITY

If you’re resetting test data and want identity values reset:

TRUNCATE TABLE users
RESTART IDENTITY;

---

Mistake 5: Using CASCADE Without Checking Dependencies

Be careful with:

TRUNCATE ... CASCADE;

and:

DROP ... CASCADE;

Both can affect related objects/tables.

---

Practical Comparison

Imagine this table:

users
--------------------------------
1 | Oliver
2 | Sophia
3 | Ethan
4 | Emma

Remove one user

DELETE FROM users
WHERE id = 2;

Result:

1 | Oliver
3 | Ethan
4 | Emma

Remove all users but keep the table

TRUNCATE TABLE users;

Result:

users table exists
0 rows

Remove the table completely

DROP TABLE users;

Result:

users table does not exist

---

Performance Summary

For typical workloads:

DELETE
→ Best for selective deletion

TRUNCATE
→ Best for quickly removing all rows

DROP
→ Best when the table itself is no longer needed

Performance should not be the only deciding factor.

You also need to consider:

Foreign keys
Triggers
Transactions
Identity sequences
Dependencies
Application behavior

---

Best Practices

Use DELETE for Selective Removal

DELETE FROM users
WHERE id = 10;

Use TRUNCATE for Full Table Reset

TRUNCATE TABLE users;

Use RESTART IDENTITY When Needed

TRUNCATE TABLE users
RESTART IDENTITY;

Use DROP Only When the Object Is No Longer Needed

DROP TABLE old_users;

Be Careful With CASCADE

Always understand what dependent tables or objects may be affected.

Use Transactions for Destructive Operations When Appropriate

For example:

BEGIN;

DELETE FROM users
WHERE id = 10;

-- Verify

COMMIT;

Test Destructive Queries

Especially in production environments.

---

Quick Cheat Sheet

DELETE

DELETE FROM table_name
WHERE condition;

Use when: You need to remove specific rows.

---

TRUNCATE

TRUNCATE TABLE table_name;

Use when: You need to remove all rows but keep the table.

---

TRUNCATE + Reset IDs

TRUNCATE TABLE table_name
RESTART IDENTITY;

Use when: You want to clear the table and reset identity sequences.

---

DROP

DROP TABLE table_name;

Use when: You no longer need the table itself.

---

Final Verdict

The difference can be remembered in three simple lines:

DELETE
→ Remove rows

TRUNCATE
→ Empty the table

DROP
→ Remove the table

Use DELETE when you need precise control over which records are removed.

Use TRUNCATE when you want to quickly empty an entire table while keeping its structure.

Use DROP when the table itself is no longer required.

The most important thing is to understand the scope of each command before executing it:

DELETE changes the data, TRUNCATE clears the data, and DROP removes the database object.