Kenhuang Academy
  • English

      • 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

      FeatureTRUNCATEDELETE
      SpeedVery fastSlower (row by row)
      LoggingMinimal loggingLogs each row deletion
      TransactionRollback-safeRollback-safe
      TriggersNot fired by defaultFires row-level triggers
      WHERE clauseNot supportedSupported
      Returns row countNoYes
      Foreign key checkRequires CASCADEAutomatic 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;