TRUNCATE

Instantly remove all rows from a table using TRUNCATE faster than DELETE with no WHERE.

Overview

TRUNCATE removes all rows from one or more tables at once. It is significantly faster than DELETE for removing all rows because it does not scan rows individually, does not fire row-level triggers, and does not generate per-row WAL entries. Instead, it deallocates the table's data pages directly. TRUNCATE in PostgreSQL is transactional it can be rolled back inside a transaction. However, unlike DELETE, TRUNCATE does not take row-level locks and can conflict with concurrent transactions that are using the table. TRUNCATE also has a RESTART IDENTITY option to reset sequences.

Key Points

•TRUNCATE table removes all rows instantly
•Faster than DELETE for removing all rows (no row scanning)
•Does not fire row-level triggers (only statement-level)
•TRUNCATE is transactional can be rolled back in PostgreSQL
•RESTART IDENTITY resets auto-increment sequences
•CASCADE also truncates related tables (via FK)

Examples

Basic TRUNCATE

-- Remove all rows from a table
TRUNCATE logs;

-- Truncate multiple tables at once
TRUNCATE sessions, temp_data;

-- Reset the SERIAL/IDENTITY sequence back to 1
TRUNCATE users RESTART IDENTITY;

-- Truncate with CASCADE for FK-related tables
TRUNCATE customers CASCADE;

TRUNCATE inside a transaction

BEGIN;
TRUNCATE users;
-- Preview: should show 0 rows
SELECT COUNT(*) FROM users;
-- Undo the truncate
ROLLBACK;
-- Users are back!
SELECT COUNT(*) FROM users;

TRUNCATE

Overview

Key Points

Examples

Exercises

Knowledge Check

Why is TRUNCATE faster than DELETE with no WHERE clause?