How to TRUNCATE TABLE in PostgreSQL

How do I quickly delete all rows from a table using TRUNCATE TABLE in PostgreSQL?

TRUNCATE TABLE instantly removes all rows from one or more tables and optionally resets identity sequences, bypassing row-level locks and logging for high-speed cleanup.

Welcome to Galaxy!
You'll be receiving a confirmation email.

In the meantime, follow us on Twitter

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why choose TRUNCATE TABLE over DELETE?

TRUNCATE TABLE is bulk, metadata-level deletion. PostgreSQL deallocates data pages instead of logging every row, making it dramatically faster and less WAL-intensive than DELETE for large datasets.

What is the basic TRUNCATE TABLE syntax?

Use TRUNCATE TABLE table_name; to wipe data instantly. Add options such as RESTART IDENTITY, CASCADE, or multiple table names for more control.

How do I reset SERIAL/IDENTITY columns?

Add RESTART IDENTITY to zero out sequence generators so new rows start at 1. Omitting it keeps sequence values untouched.

When do I need CASCADE?

Use CASCADE when child tables reference the target through foreign keys. PostgreSQL truncates dependent tables in the same transaction, preventing constraint errors.

Can I truncate multiple tables at once?

Yes. List table names comma-separated. PostgreSQL locks and truncates them atomically, ideal for clearing staging schemas after ETL jobs.

Example: Clear all orders for a fresh import

BEGIN; TRUNCATE TABLE Orders, OrderItems RESTART IDENTITY CASCADE; COMMIT;

This script erases historical order data, resets order IDs, and removes dependent order items—all in milliseconds.

Best practices for safe use

Wrap TRUNCATE in a transaction so you can ROLLBACK if needed. Always run on non-production first, verify row counts, and back up critical tables.

Should I grant TRUNCATE to everyone?

No. Restrict the privilege to roles that truly need it. TRUNCATE is irreversible once committed.

DELETE vs. TRUNCATE: quick comparison

DELETE supports WHERE clauses, fires row-level triggers, and returns affected row count. TRUNCATE is all-or-nothing, bypasses row triggers, and is non-recoverable without backups.

Why How to TRUNCATE TABLE in PostgreSQL is important

How to TRUNCATE TABLE in PostgreSQL Example Usage


-- Refresh staging tables before nightly load
BEGIN;
TRUNCATE TABLE Orders, OrderItems RESTART IDENTITY CASCADE;
TRUNCATE TABLE Customers CONTINUE IDENTITY;
COMMIT;

How to TRUNCATE TABLE in PostgreSQL Syntax


TRUNCATE [TABLE] table_name [, ...]
    [ RESTART IDENTITY | CONTINUE IDENTITY ]
    [ CASCADE | RESTRICT ];

-- Ecommerce examples
-- 1. Simple wipe
TRUNCATE TABLE Customers;

-- 2. Wipe orders and items, reset sequences, handle dependencies
TRUNCATE TABLE Orders, OrderItems
        RESTART IDENTITY CASCADE;

-- 3. Keep sequence values while truncating all products
TRUNCATE TABLE Products CONTINUE IDENTITY;

Common Mistakes

Using TRUNCATE without a transaction. Once committed, data is gone permanently. Fix: wrap TRUNCATE in BEGIN/COMMIT so you can ROLLBACK during testing.
Forgetting RESTART IDENTITY, leading to large primary keys after reloads. Fix: add RESTART IDENTITY when you need fresh sequence values.