截断 Postgres 数据库中的所有表

FrustratedWithFormsDesigner 是正确的，PL/pgSQL 可以做到这一点。这是脚本：

CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$
DECLARE
    statements CURSOR FOR
        SELECT tablename FROM pg_tables
        WHERE tableowner = username AND schemaname = 'public';
BEGIN
    FOR stmt IN statements LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;';
    END LOOP;
END;
$$ LANGUAGE plpgsql;

这将创建一个存储函数（您只需执行一次），之后您可以像这样使用它：

SELECT truncate_tables('MYUSER');

---

PL/pgSQL 中很少需要显式游标。使用 FOR loop 的更简单、更快速的隐式光标：

由于每个数据库的表名不是唯一的，因此您必须对表名进行模式限定以确保。此外，我将功能限制为默认模式“公共”。适应您的需要，但请务必排除系统架构 pg_* 和 information_schema。

请非常小心使用这些功能。他们核对您的数据库。我添加了一个儿童安全装置。注释 RAISE NOTICE 行并取消注释 EXECUTE 以启动炸弹...

CREATE OR REPLACE FUNCTION f_truncate_tables(_username text)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
DECLARE
   _tbl text;
   _sch text;
BEGIN
   FOR _sch, _tbl IN 
      SELECT schemaname, tablename
      FROM   pg_tables
      WHERE  tableowner = _username
      AND    schemaname = 'public'
   LOOP
      -- dangerous, test before you execute!
      RAISE NOTICE '%',  -- once confident, comment this line ...
      -- EXECUTE         -- ... and uncomment this one
         format('TRUNCATE TABLE %I.%I CASCADE', _sch, _tbl);
   END LOOP;
END
$func$;

format() 需要 Postgres 9.1 或更高版本。在旧版本中，像这样连接查询字符串：

'TRUNCATE TABLE ' || quote_ident(_sch) || '.' || quote_ident(_tbl)  || ' CASCADE';

单命令，无循环

由于我们可以一次TRUNCATE 多个表，因此我们根本不需要任何游标或循环：

在数组中传递表名

聚合所有表名并执行单个语句。更简单，更快：

CREATE OR REPLACE FUNCTION f_truncate_tables(_username text)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   -- dangerous, test before you execute!
   RAISE NOTICE '%',  -- once confident, comment this line ...
   -- EXECUTE         -- ... and uncomment this one
  (SELECT 'TRUNCATE TABLE '
       || string_agg(format('%I.%I', schemaname, tablename), ', ')
       || ' CASCADE'
   FROM   pg_tables
   WHERE  tableowner = _username
   AND    schemaname = 'public'
   );
END
$func$;

称呼：

SELECT truncate_tables('postgres');

精细化查询

你甚至不需要一个函数。在 Postgres 9.0+ 中，您可以在 DO 语句中执行动态命令。在 Postgres 9.5+ 中，语法可以更简单：

DO
$do$
BEGIN
   -- dangerous, test before you execute!
   RAISE NOTICE '%',  -- once confident, comment this line ...
   -- EXECUTE         -- ... and uncomment this one
   (SELECT 'TRUNCATE TABLE ' || string_agg(oid::regclass::text, ', ') || ' CASCADE'
    FROM   pg_class
    WHERE  relkind = 'r'  -- only tables
    AND    relnamespace = 'public'::regnamespace
   );
END
$do$;

关于pg_class、pg_tables和information_schema.tables的区别：

如何检查给定模式中是否存在表

关于 regclass 和引用的表名：

表名作为 PostgreSQL 函数参数

重复使用

使用您的原版结构和所有空表创建一个“模板”数据库（我们将其命名为 my_template）。然后经历一个 DROP / CREATE DATABASE 循环：

DROP DATABASE mydb;
CREATE DATABASE mydb TEMPLATE my_template;

这是非常快的，因为 Postgres 在文件级别复制整个结构。没有并发问题或其他开销减慢您的速度。

如果并发连接阻止您删除数据库，请考虑：

强制删除数据库，而其他人可能已连接

---

如果我必须这样做，我将简单地创建当前 db 的模式 sql，然后删除并创建 db，然后使用模式 sql 加载 db。

以下是涉及的步骤：

1) 创建数据库的模式转储 (--schema-only)

pg_dump mydb -s > schema.sql

2) 删除数据库

drop database mydb;

3) 创建数据库

create database mydb;

4) 导入架构

psql mydb < schema.sql

---

只需执行以下查询：

DO $$ DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(r.tablename) || '';
    END LOOP;
END $$;

---

在这种情况下，最好只使用一个空数据库作为模板，当您需要刷新时，删除现有数据库并从模板创建一个新数据库。

---

伙计们，更好更干净的方法是：

1) 创建数据库的模式转储 (--schema-only) pg_dump mydb -s > schema.sql

2）删除数据库删除数据库mydb；

3）创建数据库创建数据库mydb；

4) 导入模式 psql mydb < schema.sql

这对我有用！

祝你今天过得愉快。海勒姆·沃克

---

能否使用动态 SQL 依次执行每条语句？您可能必须编写一个 PL/pgSQL 脚本来执行此操作。

http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html（第 38.5.4 节。执行动态命令）

---

清洁 AUTO_INCREMENT 版本：

CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$
DECLARE
    statements CURSOR FOR
        SELECT tablename FROM pg_tables
        WHERE tableowner = username AND schemaname = 'public';
BEGIN
    FOR stmt IN statements LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;';

        IF EXISTS (
            SELECT column_name 
            FROM information_schema.columns 
            WHERE table_name=quote_ident(stmt.tablename) and column_name='id'
        ) THEN
           EXECUTE 'ALTER SEQUENCE ' || quote_ident(stmt.tablename) || '_id_seq RESTART WITH 1';
        END IF;

    END LOOP;
END;
$$ LANGUAGE plpgsql;

---

您也可以使用 bash 执行此操作：

#!/bin/bash
PGPASSWORD='' psql -h 127.0.0.1 -Upostgres sng --tuples-only --command "SELECT 'TRUNCATE TABLE ' || schemaname || '.' ||  tablename || ';' FROM pg_tables WHERE schemaname in ('cms_test', 'ids_test', 'logs_test', 'sps_test');" | 
tr "\\n" " " | 
xargs -I{} psql -h 127.0.0.1 -Upostgres sng --command "{}"

您将需要调整架构名称、密码和用户名以匹配您的架构。

---

如果可以使用psql，则可以使用\gexec meta 命令执行查询输出；

SELECT
    format('TRUNCATE TABLE %I.%I', ns.nspname, c.relname)
  FROM pg_namespace ns 
  JOIN pg_class c ON ns.oid = c.relnamespace
  JOIN pg_roles r ON r.oid = c.relowner
  WHERE
    ns.nspname = 'table schema' AND                               -- add table schema criteria 
    r.rolname = 'table owner' AND                                 -- add table owner criteria
    ns.nspname NOT IN ('pg_catalog', 'information_schema') AND    -- exclude system schemas
    c.relkind = 'r' AND                                           -- tables only
    has_table_privilege(c.oid, 'TRUNCATE')                        -- check current user has truncate privilege
  \gexec 

请注意，9.6 版中引入了 \gexec

---

要删除数据并保留 pgAdmin 中的表结构，您可以执行以下操作：

右键单击数据库 -> 备份，选择“仅架构”

删除数据库

创建一个新数据库并像以前一样命名它

右键新建数据库->还原->选择备份，选择“Schema only”

---

您可以使用类似的方法来获取所有截断查询。

SELECT 'TRUNCATE TABLE ' ||  table_name || ';' 
  FROM information_schema.tables
 WHERE table_schema='schema_name'
   AND table_type='BASE TABLE';

---