TRUNCATE
TRUNCATE quickly removes all rows from a table, much faster than DELETE.
In this page:
Syntax
TRUNCATE TABLE table_name;
TRUNCATE TABLE table_name RESTART IDENTITY;TRUNCATE
TRUNCATE table empties one or more tables by discarding their data files rather than deleting row by row. RESTART IDENTITY resets identity or serial counters, and CASCADE also truncates tables that reference it.
TRUNCATE is transactional in PostgreSQL but takes a strong lock, and it fires no row-level delete triggers.
Note:
- TRUNCATE ...
- RESTART IDENTITY resets auto-numbering.
Example: TRUNCATE
shop=# SELECT COUNT(*) FROM log_entries;
count
---------
2500000
shop=# TRUNCATE TABLE log_entries RESTART IDENTITY;
TRUNCATE TABLE
shop=# SELECT COUNT(*) FROM log_entries;
count
-------
0
shop=# BEGIN;
shop=*# TRUNCATE orders CASCADE;
NOTICE: truncate cascades to table "order_items"
shop=*# ROLLBACK;⚠️ Run this in your own terminal or Node.js environment.
Related Topics
Common Mistakes
- Using TRUNCATE where you need a WHERE clause
- Forgetting CASCADE when foreign keys exist
- Not realising it takes an exclusive lock
Chapter Summary
- TRUNCATE removes all rows fast
- RESTART IDENTITY resets counters
- CASCADE follows foreign keys
- It takes an ACCESS EXCLUSIVE lock
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: