PostgreSQL

如何使用 psql 清空多张表

约 3 小时前发布

在 psql 中编写脚本清空多个表，有几种成熟的方案，取决于你的具体需求（是固定表名还是动态获取，是否需要处理外键等）。

方案一：静态脚本（固定表名）

如果表名是固定的，直接创建一个 .sql 文件即可。

truncate_tables.sql

-- 开启事务，方便检查或回滚
BEGIN;

-- 清空指定的表并重置序列
TRUNCATE TABLE table1, table2, table3 RESTART IDENTITY CASCADE;

-- 如果确认无误，提交事务
COMMIT;

-- 如果出错或不想要更改，执行 ROLLBACK;

执行方式：

psql -U username -d database_name -f truncate_tables.sql

或者在 psql 内部：

\i truncate_tables.sql

方案二：动态脚本（清空某个模式下的所有表）

如果表名不固定，或想清空一个模式下所有表，可以使用 DO 语句动态生成 TRUNCATE 命令并执行。

truncate_all.sql

DO $$
DECLARE
    table_rec RECORD;
BEGIN
    -- 清空 public 模式下所有表
    FOR table_rec IN 
        SELECT tablename 
        FROM pg_tables 
        WHERE schemaname = 'public'
    LOOP
        EXECUTE format('TRUNCATE TABLE %I RESTART IDENTITY CASCADE;', table_rec.tablename);
    END LOOP;
END $$;

执行方式：

psql -U username -d database_name -f truncate_all.sql

缺点：循环逐条执行 TRUNCATE，速度比单条命令稍慢，且每条都会获取锁。

方案三：生成单条 TRUNCATE 命令（推荐）

更优的做法是用脚本生成一条包含所有表的 TRUNCATE 命令，然后执行它。这样效率最高，且只获取一次锁。

generate_and_truncate.sql

-- 先生成 TRUNCATE 命令
WITH table_list AS (
    SELECT string_agg(quote_ident(tablename), ', ') AS table_names
    FROM pg_tables
    WHERE schemaname = 'public'
)
-- 然后执行它
SELECT format('TRUNCATE TABLE %s RESTART IDENTITY CASCADE;', table_names)
FROM table_list \gset

-- 执行生成的命令
\if :{?table_names}
    EXECUTE :'truncate_command';
\endif

注意：\gset 是 psql 的元命令，会将查询结果赋给变量。这种方法依赖 psql 特性，不能用于普通 SQL 客户端。

方案四：Shell 脚本 + psql（更灵活）

使用 Shell 脚本从数据库获取表名列表，然后生成并执行命令。

truncate.sh

#!/bin/bash

DB_USER="your_user"
DB_NAME="your_db"
SCHEMA="public"

# 获取所有表名并用逗号拼接
TABLES=$(psql -U $DB_USER -d $DB_NAME -t -A -c "
    SELECT string_agg(quote_ident(tablename), ', ')
    FROM pg_tables
    WHERE schemaname = '$SCHEMA';
")

# 执行 TRUNCATE
psql -U $DB_USER -d $DB_NAME -c "TRUNCATE TABLE $TABLES RESTART IDENTITY CASCADE;"

执行：

chmod +x truncate.sh
./truncate.sh

方案五：排除特定表

有时需要保留某些表（如配置表、字典表），可以添加排除条件。

动态脚本（排除 config 和 users 表）

DO $$
DECLARE
    table_rec RECORD;
BEGIN
    FOR table_rec IN 
        SELECT tablename 
        FROM pg_tables 
        WHERE schemaname = 'public'
          AND tablename NOT IN ('config', 'users')  -- 排除这些表
    LOOP
        EXECUTE format('TRUNCATE TABLE %I RESTART IDENTITY CASCADE;', table_rec.tablename);
    END LOOP;
END $$;

或者生成命令时排除：

SELECT 'TRUNCATE TABLE ' || 
       string_agg(quote_ident(tablename), ', ') || 
       ' RESTART IDENTITY CASCADE;'
FROM pg_tables
WHERE schemaname = 'public'
  AND tablename NOT IN ('config', 'users');

场景	推荐方案
表名固定，且数量少	方案一（静态脚本）
需要清空所有表，且对性能有要求	方案四（Shell 生成单条命令）
需要在 `psql` 内部一次性完成	方案三（\gset 变量方式）
需要动态排除某些表	方案五（排除条件）

安全提示

建议在事务中执行，方便出问题时回滚：
```
BEGIN;
TRUNCATE ...;
-- 检查确认后
COMMIT;
```

使用 CASCADE 要谨慎，它会清空所有依赖表，最好先查询哪些表会被级联影响：

SELECT
    conrelid::regclass AS dependent_table,
    confrelid::regclass AS referenced_table
FROM pg_constraint
WHERE confrelid::regclass = 'your_table'::regclass
  AND contype = 'f';

如果表数量极大（数百张），TRUNCATE 单条命令可能过长，可以分批执行（每次 20-30 张表）。