截断 Postgres 数据库中的所有表 [英] Truncating all tables in a Postgres database

问题描述

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

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

目前我已经设法想出了一个返回我需要执行的所有命令的 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.

推荐答案

FrustratedWithFormsDesigner 是正确的，PL/pgSQL 可以做到这一点.这是脚本:

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

这篇关于截断 Postgres 数据库中的所有表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！