当前位置：文江博客话题详情

截断 Postgres 数据库中的所有表

发布于 2024-09-01 14:40:35 字数 254 浏览 10 评论 0 原文

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

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

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

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

原文

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

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

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

勿忘初心 2024-09-08 14:40:35

FrusteratedWithFormsDesigner 是正确的，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');

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

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

回复收藏 0 原文

烟燃烟灭 2024-09-08 14:40:35

PL/pgSQL 中很少需要显式游标。使用隐式游标 ="noreferrer">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';

单个命令，无循环

因为我们可以 < strong>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< 中执行动态命令/code> 声明。在 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 在文件级别复制整个结构。没有并发问题或其他开销会减慢您的速度。

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

在其他可能连接的情况下强制删除数据库

Explicit cursors are rarely needed in PL/pgSQL. Use the simpler and faster implicit cursor of a FOR loop:

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.

Be very careful with these functions. They nuke your database. I added a child safety device. Comment the RAISE NOTICE line and uncomment EXECUTE to prime the bomb ...

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() requires Postgres 9.1 or later. In older versions concatenate the query string like this:

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

Single command, no loop

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

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

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 DO statement. And in Postgres 9.5+ the syntax can be even simpler:

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

About the difference between pg_class, pg_tables and information_schema.tables:

How to check if a table exists in a given schema

About regclass and quoted table names:

Table name as a PostgreSQL function parameter

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 DATABASE cycle:

DROP DATABASE mydb;
CREATE DATABASE mydb TEMPLATE my_template;

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

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

Force drop db while others may be connected

回复收藏 0 原文

下壹個目標 2024-09-08 14:40:35

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

以下是涉及的步骤：

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

pg_dump mydb -s > schema.sql

2) 删除数据库

drop database mydb;

3) 创建数据库

create database mydb;

4) 导入架构

psql mydb psql mydb 架构.sql

回复收藏 0 原文

扮仙女 2024-09-08 14:40:35

只需执行以下查询：

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

Just execute the query bellow:

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

回复收藏 0 原文

野生奥特曼 2024-09-08 14:40:35

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

回复收藏 0 原文

波浪屿的海角声 2024-09-08 14:40:35

简单地说，你可以运行这段 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 $;

Simply, you can run this piece of 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 $;

回复收藏 0 原文

挽你眉间 2024-09-08 14:40:35

我在这里没有看到的一件事是截断然后重置序列。请注意，像此处给出的所有内容一样的简单截断只会截断表，但会将序列保留为截断前的值。要在截断时将序列重置为其起始值，请执行以下操作：

TRUNCATE TABLE table_name RESTART IDENTITY CASCADE;

您只需将 RESTART IDENTITY 添加到您喜欢的任何答案中，无需在此处重复。 CASCADE 可以解决您可能面临的任何外键约束。

One thing that I don't see here is truncating and then resetting sequences. Note that a simple truncate like all that have been given here will just truncate the tables, but will leave sequences at their pre-truncate values. To reset the sequences to their start values when you truncate do:

TRUNCATE TABLE table_name RESTART IDENTITY CASCADE;

you can just add that RESTART IDENTITY to any of the answers you fancy, no need to repeat that here. CASCADE is there for any foreign key constraints you may face.

回复收藏 0 原文

韵柒 2024-09-08 14:40:35

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

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

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

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

4) 导入架构
psql mydb <; schema.sql

这对我有用！

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

回复收藏 0 原文

相守太难 2024-09-08 14:40:35

清理 AUTO_INCRMENT 版本：

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;

Cleaning AUTO_INCREMENT version:

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;

回复收藏 0 原文

盗心人 2024-09-08 14:40:35

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

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

回复收藏 0 原文

疯到世界奔溃 2024-09-08 14:40:35

您也可以使用 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 can do this with bash also:

#!/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.

回复收藏 0 原文

星星的轨迹 2024-09-08 14:40:35

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

右键单击数据库 ->备份，选择“仅架构”
删除数据库
创建一个新数据库并像以前那样命名
右键单击新数据库->;恢复->选择备份，选择“仅架构”

回复收藏 0 原文

忆梦 2024-09-08 14:40:35

如果您可以使用 psql 您可以使用 \gexec< /code> 执行查询输出的元命令；

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 版本中引入的

If you can use psql you can use \gexec meta command to execute query output;

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 \gexec is introduced into the version 9.6

回复收藏 0 原文

南…巷孤猫 2024-09-08 14:40:35

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

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

You can use something like this to get all truncate queries.

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

回复收藏 0 原文

~没有更多了~

关于作者

无远思近则忧

暂无简介

0 文章

0 评论

23 人气

关注发私信

友情链接

文江博客

截断 Postgres 数据库中的所有表

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（14）

单个命令，无循环

精炼查询

用于重复使用

Single command, no loop

Refined query

For repeated use

关于作者

相关话题

热门标签

推荐作者

烙印

singlesman

给自己一个微笑

独孤求败

晨钟暮鼓

我是自愿种绣球花的

友情链接

截断 Postgres 数据库中的所有表

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（14）

单个命令，无循环

精炼查询

用于重复使用

Single command, no loop

Refined query

For repeated use

关于作者

相关话题

热门标签

推荐作者

烙印

singlesman

给自己一个微笑

独孤求败

晨钟暮鼓

我是自愿种绣球花的

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。