截断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.sql

2）删除数据库

drop database mydb;

3）创建数据库

create database mydb;

4）导入架构

psql mydb < schema.sql