截断Postgres数据库中的所有表

我经常需要在重建之前删除PostgreSQL数据库中的所有数据。如何在SQL中直接做到这一点?

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

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

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

228979 次浏览
小开

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

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

小开
最佳答案

挫折withformsdesigner是正确的，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 < / >强循环中更简单快捷的隐式游标:

由于表名在每个数据库中不是唯一的，因此必须对表名进行模式限定以确保这一点。此外，我将该函数限制为默认模式“public”。根据您的需要进行调整，但一定要排除系统模式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和引用表名:

重复使用

用你的香草结构和所有空表创建一个“template"数据库(让我们命名为my_template)。然后进行__ABC1 / CREATE DATABASE .循环:

DROP DATABASE mydb;
CREATE DATABASE mydb TEMPLATE my_template;

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

如果并发连接阻止你删除数据库，考虑:

小开

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

您需要调整模式名称、密码和用户名以匹配您的模式。

小开

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

以下是所涉及的步骤:

1)创建数据库的架构转储(--schema-only)

pg_dump mydb -s > schema.sql

2)删除数据库

drop database mydb;

3)创建数据库

create database mydb;

4)导入模式

psql mydb < schema.sql

小开

要删除数据并保存pgAdmin中的表结构，您可以执行:

  • 右键单击database -> backup，选择"Schema only"
  • 删除数据库
  • 创建一个新的数据库，并将其命名为前者
  • 右键单击新数据库->恢复->选择备份，选择“Schema only”
小开

清洗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;
小开

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

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

.sql

2)删除数据库 Drop database mydb;

3)创建数据库 创建数据库mydb;

4)导入Schema mydb <schema.sql < / p >

这是我的工作!

祝你今天过得愉快。 希兰沃克< / p >
小开

如果你可以使用psql，你可以使用\gexec元命令来执行查询输出;

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

注意，\gexec是在9.6版本中引入的

小开

只需执行下面的查询:

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 $$;
小开

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

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

我在这里没有看到的一件事是截断然后重置序列。请注意，像这里给出的所有截断一样，简单的截断只会截断表，但会使序列保持截断前的值。要在截断do时将序列重置为起始值:

TRUNCATE TABLE table_name RESTART IDENTITY CASCADE;

你可以把这个RESTART IDENTITY添加到任何你想要的答案中，没有必要在这里重复。CASCADE是针对您可能面临的任何外键约束的。

小开

简单地说，你可以运行下面这段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) || ' CASCADE';
END LOOP;
END $$;

提交答案