PostgreSQL 清空表 TRUNCATE TABLE

概述

TRUNCATE TABLE语句用于快速删除表中的所有数据，但保留表结构。相比DELETE，TRUNCATE更快速高效，特别适用于清空大型表。

TRUNCATE语句

基本语法

TRUNCATE TABLE table_name;

简单示例

-- 清空表
TRUNCATE TABLE users;

-- 可以省略TABLE关键字
TRUNCATE users;

完整语法

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

选项说明

重置序列

-- 重置自增序列到初始值
TRUNCATE TABLE users RESTART IDENTITY;

-- 保持序列当前值（默认）
TRUNCATE TABLE users CONTINUE IDENTITY;

级联清空

-- 同时清空有外键引用的表
TRUNCATE TABLE departments CASCADE;

-- 如果有外键引用则拒绝清空（默认）
TRUNCATE TABLE departments RESTRICT;

清空多个表

-- 一次清空多个表
TRUNCATE TABLE orders, order_items, users;

-- 带选项清空多个表
TRUNCATE TABLE orders, order_items RESTART IDENTITY CASCADE;

TRUNCATE vs DELETE

性能对比

特性	TRUNCATE	DELETE
速度	非常快	较慢（逐行删除）
日志	最小日志	记录每行删除
事务	可回滚	可回滚
触发器	默认不触发	触发行级触发器
WHERE条件	不支持	支持
返回行数	不返回	返回删除行数
外键检查	需要CASCADE	自动检查

使用场景

-- 使用TRUNCATE：清空整个表
TRUNCATE TABLE logs RESTART IDENTITY;

-- 使用DELETE：按条件删除
DELETE FROM logs WHERE created_at < '2024-01-01';

实际应用示例

清空测试数据

-- 清空测试表并重置ID
TRUNCATE TABLE test_users, test_orders RESTART IDENTITY CASCADE;

数据重置脚本

-- 事务中清空多表
BEGIN;

-- 禁用外键检查（通过CASCADE）
TRUNCATE TABLE order_items CASCADE;
TRUNCATE TABLE orders CASCADE;
TRUNCATE TABLE products RESTART IDENTITY;
TRUNCATE TABLE users RESTART IDENTITY;

COMMIT;

定期清理日志表

-- 清空旧日志并重置序列
TRUNCATE TABLE audit_logs RESTART IDENTITY;
TRUNCATE TABLE access_logs RESTART IDENTITY;

触发器行为

默认不触发行级触发器

-- TRUNCATE不会触发行级DELETE触发器
-- 但可以创建TRUNCATE触发器
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();

外键约束处理

有外键引用时

-- 方法1：使用CASCADE
TRUNCATE TABLE departments CASCADE;

-- 方法2：先清空引用表
TRUNCATE TABLE employees;
TRUNCATE TABLE departments;

-- 方法3：临时禁用触发器
ALTER TABLE employees DISABLE TRIGGER ALL;
TRUNCATE TABLE departments;
ALTER TABLE employees ENABLE TRIGGER ALL;

注意事项

1. 权限要求：需要表的TRUNCATE权限或所有者权限
2. 锁定：TRUNCATE会获取ACCESS EXCLUSIVE锁
3. MVCC：TRUNCATE是MVCC安全的
4. 分区表：可以TRUNCATE单个分区
5. 复制：在逻辑复制中，TRUNCATE会被复制

权限设置

-- 授予TRUNCATE权限
GRANT TRUNCATE ON users TO developer;

-- 撤销TRUNCATE权限
REVOKE TRUNCATE ON users FROM developer;

常见错误

外键约束错误

-- 错误信息
ERROR: cannot truncate a table referenced in a foreign key constraint

-- 解决方法
TRUNCATE TABLE parent_table CASCADE;

权限不足

-- 错误信息
ERROR: permission denied for table users

-- 解决方法：授予权限
GRANT TRUNCATE ON users TO username;