PostgreSQL Truncate Table

Overview

The TRUNCATE TABLE statement quickly removes all data from a table while preserving the table structure. Compared to DELETE, TRUNCATE is faster and more efficient, especially for large tables.

TRUNCATE Statement

Basic Syntax

TRUNCATE TABLE table_name;

Simple Examples

-- Truncate a table
TRUNCATE TABLE users;

-- TABLE keyword is optional
TRUNCATE users;

Complete Syntax

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

Options Explained

Reset Sequences

-- Reset auto-increment sequence to initial value
TRUNCATE TABLE users RESTART IDENTITY;

-- Keep current sequence value (default)
TRUNCATE TABLE users CONTINUE IDENTITY;

Cascade Truncate

-- Also truncate tables with foreign key references
TRUNCATE TABLE departments CASCADE;

-- Refuse to truncate if foreign key references exist (default)
TRUNCATE TABLE departments RESTRICT;

Truncating Multiple Tables

-- Truncate multiple tables at once
TRUNCATE TABLE orders, order_items, users;

-- Truncate multiple tables with options
TRUNCATE TABLE orders, order_items RESTART IDENTITY CASCADE;

TRUNCATE vs DELETE

Performance Comparison

Feature	TRUNCATE	DELETE
Speed	Very fast	Slower (row by row)
Logging	Minimal logging	Logs each row deletion
Transaction	Rollback-safe	Rollback-safe
Triggers	Not fired by default	Fires row-level triggers
WHERE clause	Not supported	Supported
Returns row count	No	Yes
Foreign key check	Requires CASCADE	Automatic check

Use Case Examples

-- Use TRUNCATE: Clear entire table
TRUNCATE TABLE logs RESTART IDENTITY;

-- Use DELETE: Delete with conditions
DELETE FROM logs WHERE created_at < '2024-01-01';

Practical Examples

Clearing Test Data

-- Clear test tables and reset IDs
TRUNCATE TABLE test_users, test_orders RESTART IDENTITY CASCADE;

Data Reset Script

-- Truncate multiple tables in transaction
BEGIN;

-- Disable foreign key checks (via CASCADE)
TRUNCATE TABLE order_items CASCADE;
TRUNCATE TABLE orders CASCADE;
TRUNCATE TABLE products RESTART IDENTITY;
TRUNCATE TABLE users RESTART IDENTITY;

COMMIT;

Periodic Log Cleanup

-- Clear old logs and reset sequences
TRUNCATE TABLE audit_logs RESTART IDENTITY;
TRUNCATE TABLE access_logs RESTART IDENTITY;

Trigger Behavior

Row-Level Triggers Not Fired by Default

-- TRUNCATE does not fire row-level DELETE triggers
-- But you can create TRUNCATE triggers
CREATE OR REPLACE FUNCTION log_truncate()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO truncate_log (table_name, truncated_at)
    VALUES (TG_TABLE_NAME, CURRENT_TIMESTAMP);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

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

Handling Foreign Key Constraints

When Foreign Key References Exist

-- Method 1: Use CASCADE
TRUNCATE TABLE departments CASCADE;

-- Method 2: Truncate referencing tables first
TRUNCATE TABLE employees;
TRUNCATE TABLE departments;

-- Method 3: Temporarily disable triggers
ALTER TABLE employees DISABLE TRIGGER ALL;
TRUNCATE TABLE departments;
ALTER TABLE employees ENABLE TRIGGER ALL;

Important Notes

1. Permission required: Requires TRUNCATE privilege or owner rights on the table
2. Locking: TRUNCATE acquires ACCESS EXCLUSIVE lock
3. MVCC: TRUNCATE is MVCC-safe
4. Partitioned tables: Can TRUNCATE individual partitions
5. Replication: In logical replication, TRUNCATE is replicated

Permission Settings

-- Grant TRUNCATE privilege
GRANT TRUNCATE ON users TO developer;

-- Revoke TRUNCATE privilege
REVOKE TRUNCATE ON users FROM developer;

Common Errors

Foreign Key Constraint Error

-- Error message
ERROR: cannot truncate a table referenced in a foreign key constraint

-- Solution
TRUNCATE TABLE parent_table CASCADE;

Insufficient Permissions

-- Error message
ERROR: permission denied for table users

-- Solution: Grant permission
GRANT TRUNCATE ON users TO username;