postgresql 截断 Postgres 数据库中的所有表

Question

提问by Sig

I regularly need to delete all the data from my PostgreSQL database before a rebuild. How would I do this directly in SQL?

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

At the moment I've managed to come up with a SQL statement that returns all the commands I need to execute:

目前我已经设法想出了一个 SQL 语句，它返回我需要执行的所有命令：

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

But I can't see a way to execute them programmatically once I have them.

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

Answer 1

回答by Henning

FrustratedWithFormsDesigner is correct, PL/pgSQL can do this. Here's the script:

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;

This creates a stored function (you need to do this just once) which you can afterwards use like this:

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

SELECT truncate_tables('MYUSER');

Answer 2

回答by Erwin Brandstetter

Explicit cursors are rarely needed in plpgsql. Use the simpler and faster implicit cursorof a FORloop:

plpgsql 中很少需要显式游标。使用更简单、更快的循环隐式游标FOR：

Note:Since table names are not unique per database, you have to schema-qualify table names to be sure. Also, I limit the function to the default schema 'public'. Adapt to your needs, but be sure to exclude the system schemas pg_*and information_schema.

注意：由于每个数据库的表名不是唯一的，因此您必须对表名进行模式限定才能确定。此外，我将该功能限制为默认模式“public”。适应您的需求，但一定要排除系统架构pg_*和information_schema.

Be very carefulwith these functions. They nuke your database. I added a child safety device. Comment the RAISE NOTICEline and uncomment EXECUTEto prime the bomb ...

使用这些功能时要非常小心。他们核爆你的数据库。我加了一个儿童安全装置。注释该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()requires Postgres 9.1 or later. In older versions concatenate the query string like this:

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

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

Single command, no loop

单命令，无循环

Since we can TRUNCATEmultiple tables at once we don't need any cursor or loop at all:

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

Passing table names in an array

在数组中传递表名

Aggregate all table names and execute a single statement. Simpler, faster:

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

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;

Call:

称呼：

SELECT truncate_tables('postgres');

Refined query

细化查询

You don't even need a function. In Postgres 9.0+ you can execute dynamic commands in a DOstatement. And in Postgres 9.5+ the syntax can be even simpler:

你甚至不需要一个函数。在 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$;

About the difference between pg_class, pg_tablesand information_schema.tables:

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

How to check if a table exists in a given schema

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

About regclassand quoted table names:

关于regclass和引用的表名：

Table name as a PostgreSQL function parameter

表名作为 PostgreSQL 函数参数

For repeated use

重复使用

Create a "template" database(let's name it my_template) with your vanilla structure and all empty tables. Then go through a DROP/ CREATE DATABASEcycle:

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

DROP DATABASE mydb;
CREATE DATABASE mydb TEMPLATE my_template;

This is extremelyfast, because Postgres copies the whole structure on the file level. No concurrency issues or other overhead slowing you down.

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

If concurrent connections keep you from dropping the DB, consider:

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

Force drop db while others may be connected

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

Answer 3

回答by Sandip Ransing

If I have to do this, I will simply create a schema sql of current db, then drop & create db, then load db with schema sql.

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

Below are the steps involved:

以下是涉及的步骤：

1) Create Schema dump of database (--schema-only)

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

pg_dump mydb -s > schema.sql

2) Drop database

2) 删除数据库

drop database mydb;

3) Create Database

3) 创建数据库

create database mydb;

4) Import Schema

4) 导入架构

psql mydb < schema.sql

Answer 4

回答by Scott Bailey

In this case it would probably be better to just have an empty database that you use as a template and when you need to refresh, drop the existing database and create a new one from the template.

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

Answer 5

回答by FrustratedWithFormsDesigner

Could you use dynamic SQL to execute each statement in turn? You would probably have to write a PL/pgSQL script to do this.

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

http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html(section 38.5.4. Executing Dynamic Commands)

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

Answer 6

回答by simao

You can do this with bash also:

你也可以用 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 "{}"

You will need to adjust schema names, passwords and usernames to match your schemas.

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

Answer 7

回答by RomanGorbatko

Cleaning AUTO_INCREMENTversion:

清洁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;

Answer 8

回答by Hiram Walker

Guys the better and clean way is to :

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

1) Create Schema dump of database (--schema-only) pg_dump mydb -s > schema.sql

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

2) Drop database drop database mydb;

2) drop database drop database mydb；

3) Create Database create database mydb;

3）创建数据库create database mydb；

4) Import Schema psql mydb < schema.sql

4) 导入 Schema psql mydb < schema.sql

It′s work for me!

这对我有用！

Have a nice day. Hiram Walker

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

Answer 9

回答by Sahap Asci

If you can use psqlyou can use \gexecmeta command to execute query output;

如果你可以使用psql你可以使用\gexecmeta 命令来执行查询输出；

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

Note that \gexecis introduced into the version 9.6

注意\gexec是9.6版本引入的

Answer 10

回答by mYnDstrEAm

For removing the data and preserving the table-structures in pgAdminyou can do:

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

Right-click database -> backup, select "Schema only"
Drop the database
Create a new database and name it like the former
Right-click the new database -> restore -> select the backup, select "Schema only"

右键数据库->备份，选择“Schema only”
删除数据库
创建一个新的数据库并像前者一样命名
右键新建数据库->恢复->选择备份，选择“Schema only”

postgresql 截断 Postgres 数据库中的所有表

提问by Sig

回答by Henning

回答by Erwin Brandstetter

Single command, no loop

单命令，无循环

Refined query

细化查询

For repeated use

重复使用

回答by Sandip Ransing

回答by Scott Bailey

回答by FrustratedWithFormsDesigner

回答by simao

回答by RomanGorbatko

回答by Hiram Walker

回答by Sahap Asci

回答by mYnDstrEAm

相关推荐

最近更新

标签

postgresql 截断 Postgres 数据库中的所有表

提问by Sig

回答by Henning

回答by Erwin Brandstetter

Single command, no loop

单命令，无循环

Refined query

细化查询

For repeated use

重复使用

回答by Sandip Ransing

回答by Scott Bailey

回答by FrustratedWithFormsDesigner

回答by simao

回答by RomanGorbatko

回答by Hiram Walker

回答by Sahap Asci

回答by mYnDstrEAm

相关推荐

postgresql 删除 pgSQL 中的数组值

postgresql 错误：查询没有结果数据的目的地

PostgreSQL：改进 pg_dump、pg_restore 性能

postgresql PL/pgSQL 中的 EXECUTE...USING 语句不适用于记录类型？

相关推荐

最近更新

标签