截断Postgres数据库中的所有表

Question

重建之前，我通常需要从PostgreSQL数据库中删除所有数据。我将如何直接在SQL中执行此操作？

目前，我设法提出了一条SQL语句，该语句返回我需要执行的所有命令：

SELECT 'TRUNCATE TABLE ' ||  tablename || ';' FROM pg_tables WHERE tableowner='MYUSER';

但是一旦有了它们，我就看不到以编程方式执行它们的方法。

Answer 1

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');

Answer 2

在plpgsql中很少需要显式游标。使用更简单，更快的循环隐式游标FOR：注意：由于表名在每个数据库中都不唯一，因此必须对表名进行模式限定。另外，我将功能限制为默认模式“ public”。适应您的需求，但请确保排除系统架构pg_*和information_schema。这些功能要非常小心。他们破坏您的数据库。我添加了儿童安全装置。评论RAISE NOTICE界线和取消EXECUTE引爆炸弹的评论...CREATE OR REPLACE FUNCTION f_truncate_tables(_username text)  RETURNS void 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      RAISE NOTICE '%',      -- EXECUTE  -- dangerous, test before you execute!         format('TRUNCATE TABLE %I.%I CASCADE', _sch, _tbl);   END LOOP;END$func$ LANGUAGE plpgsql;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 AS$func$BEGIN   RAISE NOTICE '%',    -- EXECUTE  -- dangerous, test before you execute!  (SELECT 'TRUNCATE TABLE '       || string_agg(format('%I.%I', schemaname, tablename), ', ')       || ' CASCADE'   FROM   pg_tables   WHERE  tableowner = _username   AND    schemaname = 'public'   );END$func$ LANGUAGE plpgsql;呼叫：SELECT truncate_tables('postgres');细化查询您甚至不需要功能。在Postgres 9.0+中，您可以在DO语句中执行动态命令。在Postgres 9.5+中，语法甚至可以更简单：DO$func$BEGIN   RAISE NOTICE '%',    -- EXECUTE   (SELECT 'TRUNCATE TABLE ' || string_agg(oid::regclass::text, ', ') || ' CASCADE'    FROM   pg_class    WHERE  relkind = 'r'  -- only tables    AND    relnamespace = 'public'::regnamespace   );END$func$;关于之间的差异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

Answer 3

如果必须执行此操作，则只需创建当前数据库的模式sql，然后删除并创建数据库，然后使用模式sql加载db。以下是涉及的步骤：1）创建数据库的模式转储（--schema-only）pg_dump mydb -s > schema.sql2）删除数据库drop database mydb;3）创建数据库create database mydb;4）导入架构psql mydb < schema.sql