How to Truncate a Table in SQL

What you’ll build or solve

You’ll remove every row from a table using a single bulk command.

When this approach works best

Truncating a table works best when you:

• Clear test or staging data before loading fresh records
• Reset a logging or temporary table
• Prepare a table for a full data reload

For example, you might truncate a logs table before importing new logs, or clear a staging_users table between batch jobs.

This is a bad idea if you need to delete only certain rows. In that case, use DELETE with a WHERE clause.

Prerequisites

• Access to the database
• Permission to truncate or alter tables
• Basic SQL knowledge

Step-by-step instructions

Step 1: Truncate the table

Use the standard syntax supported by most databases:

TRUNCATETABLE logs;

This removes all rows in one operation. The table definition, indexes, and constraints remain.

MySQL or PostgreSQL

TRUNCATETABLE logs;

PostgreSQL with identity reset

If the table uses a sequence or identity column and you want to restart it:

TRUNCATETABLE logs RESTARTIDENTITY;

SQL Server

TRUNCATETABLE logs;

In SQL Server, identity values reset automatically after truncation.

What to look for

• SQLite does not support TRUNCATE TABLE. Use:

DELETEFROM logs;

This performs a full delete instead of a true truncate.

• Foreign key constraints may block truncation. In PostgreSQL, you can use:

TRUNCATETABLE logsCASCADE;

• A quick check such as:

SELECTCOUNT(*)FROM logs;

should return 0 after truncation.

• Some systems treat TRUNCATE as minimally logged, which can make rollback harder.
• Always create a backup before truncating production tables.

Examples you can copy

Example 1: Clear a logging table

You store application logs:

CREATETABLE logs (
  idINTPRIMARYKEY,
  message TEXT,
  created_atTIMESTAMP
);

Before a new test cycle:

TRUNCATETABLE logs;

The table remains, but all log entries are removed.

Example 2: Reset a staging table before import

You import CSV files into a staging table:

CREATETABLE staging_users (
  idINT,
  nameVARCHAR(100),
  emailVARCHAR(100)
);

Before each new import:

TRUNCATETABLE staging_users;

This clears old batch data in one step.

Example 3: Restart IDs in PostgreSQL

You have an auto-incrementing table:

CREATETABLE orders (
  id SERIALPRIMARYKEY,
  totalDECIMAL(10,2)
);

Clear it and reset the counter:

TRUNCATETABLE orders RESTARTIDENTITY;

New inserts begin again from 1.

Common mistakes and how to fix them

Mistake 1: Using DELETE when you need TRUNCATE

What you might do:

DELETEFROM logs;

Why it causes issues: DELETE removes rows one by one and may be slower for large tables. It also does not always reset identity values.

Correct approach:

TRUNCATETABLE logs;

Use truncate for a fast, full-table reset.

Mistake 2: Truncating a table referenced by foreign keys

What you might do:

TRUNCATETABLE customers;

Why it fails: If another table references customers, the database may block the operation.

Correct approach:

In PostgreSQL:

TRUNCATETABLE customersCASCADE;

Or remove dependent rows first.

Mistake 3: Expecting an easy rollback

What you might do: Run TRUNCATE assuming you can undo it with a simple rollback.

Why it breaks expectations: Some systems treat truncate differently from normal deletes. Recovery may require a backup.

Correct approach: Test in staging and back up production data before truncating.

Troubleshooting

• If you see “permission denied,” confirm your account has truncate or alter privileges.
• If you see a foreign key constraint error, remove or cascade dependent rows.
• If SQLite returns a syntax error for TRUNCATE, use DELETE FROM table_name.
• If identity values do not reset in PostgreSQL, add RESTART IDENTITY.

Quick recap

• Use TRUNCATE TABLE table_name; to remove all rows quickly
• Structure, indexes, and constraints remain
• PostgreSQL supports RESTART IDENTITY
• SQL Server resets identity automatically
• Foreign keys can block truncation
• Back up production data before running the command