blocks|key|1035573|text|FrustratedWithFormsDesigner是正确的，PL/pgSQL可以做到这一点。脚本如下：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1035574|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+'+%7C%7C+quote_ident(stmt.tablename)+%7C%7C+'+CASCADE;';
++++END+LOOP;
END;
$$+LANGUAGE+plpgsql;|code-block|syntax|javascript|1035575|这将创建一个存储函数(您只需执行一次)，然后可以像这样使用它：|1035576|SELECT+truncate_tables('MYUSER');|1035577|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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

<pre><code>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;
</code></pre>

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

<pre><code>SELECT truncate_tables('MYUSER');
</code></pre>

blocks|key|1035798|text|如果必须这样做，我只需创建当前数据库的模式sql，然后删除和创建数据库，然后使用模式sql加载数据库。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1035799|涉及的步骤如下：|1035800|1)创建数据库的模式转储(--schema-only)|offset|length|style|CODE|1035801|pg_dump+mydb+-s+>+schema.sql|1035802|2)删除数据库|1035803|drop+database+mydb;|1035804|3)创建数据库|1035805|create+database+mydb;|1035806|4)导入模式|1035807|psql+mydb+<+schema.sql|1035808|entityMap^0|0|0|D|D|0|0|S|0|0|0|J|0|0|0|L|0|0|0|M|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|10|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|11|8|@$F|12|G|13|H|I]]|9|@]|A|$]]|$1|J|3|K|5|6|7|14|8|@$F|15|G|16|H|I]]|9|@]|A|$]]|$1|L|3|M|5|6|7|17|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|18|8|@$F|19|G|1A|H|I]]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1B|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|1C|8|@$F|1D|G|1E|H|I]]|9|@]|A|$]]|$1|T|3|U|5|6|7|1F|8|@]|9|@]|A|$]]|$1|V|3|W|5|6|7|1G|8|@$F|1H|G|1I|H|I]]|9|@]|A|$]]|$1|X|3|-4|5|6|7|1J|8|@]|9|@]|A|$]]]|Y|$]]

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

Below are the steps involved:

1) Create Schema dump of database (<code>--schema-only</code>)

<code>pg_dump mydb -s &gt; schema.sql</code>

2) Drop database

<code>drop database mydb;</code>

3) Create Database

<code>create database mydb;</code>

4) Import Schema 

<code>psql mydb &lt; schema.sql</code>

blocks|key|1250701|text|在这种情况下，最好只有一个空数据库用作模板，当您需要刷新时，删除现有数据库并从模板创建一个新数据库。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1250702|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|1251067|text|只需执行下面的查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1251068|DO+$$+DECLARE
++++r+RECORD;
BEGIN
++++FOR+r+IN+(SELECT+tablename+FROM+pg_tables+WHERE+schemaname+=+current_schema())+LOOP
++++++++EXECUTE+'TRUNCATE+TABLE+'+%7C%7C+quote_ident(r.tablename)+%7C%7C+'';
++++END+LOOP;
END+$$;|code-block|syntax|javascript|1251069|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Just execute the query bellow:
<pre><code>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 $$;
</code></pre>

blocks|key|1035894|text|伙计们，更好的干净利落的方法是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1035895|1)创建数据库的模式转储(--+Schema+-only)+pg_dump+mydb+-s+>+schema.sql|1035896|2)+Drop数据库drop+drop+database+mydb；|1035897|3)创建数据库创建数据库mydb；|1035898|4)导入模式psql+mydb+<+schema.sql|1035899|这对我来说很有用！|1035900|祝您今天愉快。希拉姆·沃克|1035901|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|S|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|T|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|U|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|V|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|O|$]]

Guys the better and clean way is to :

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

2) Drop database
drop database mydb;

3) Create Database
create database mydb;

4) Import Schema
psql mydb &lt; schema.sql

It´s work for me!

Have a nice day.
Hiram Walker

blocks|key|1250578|text|您可以使用动态SQL来依次执行每条语句吗？您可能需要编写一个PL/pgSQL脚本来执行此操作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1250579|http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html+(第38.5.4节。执行动态命令)|offset|length|1250580|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html^0|0|0|1T|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

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

<a href="http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html" rel="nofollow noreferrer">http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html</a> (section 38.5.4. Executing Dynamic Commands)

blocks|key|1250891|text|清理AUTO_INCREMENT版本：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1250892|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+'+%7C%7C+quote_ident(stmt.tablename)+%7C%7C+'+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+'+%7C%7C+quote_ident(stmt.tablename)+%7C%7C+'_id_seq+RESTART+WITH+1';
++++++++END+IF;

++++END+LOOP;
END;
$$+LANGUAGE+plpgsql;|code-block|syntax|javascript|1250893|entityMap^0|2|E|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

Cleaning <code>AUTO_INCREMENT</code> version:

<pre><code>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;
</code></pre>

blocks|key|1035693|text|您也可以使用bash来完成此操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1035694|#!/bin/bash
PGPASSWORD=''+psql+-h+127.0.0.1+-Upostgres+sng+--tuples-only+--command+"SELECT+'TRUNCATE+TABLE+'+%7C%7C+schemaname+%7C%7C+'.'+%7C%7C++tablename+%7C%7C+';'+FROM+pg_tables+WHERE+schemaname+in+('cms_test',+'ids_test',+'logs_test',+'sps_test');"+%7C+
tr+"\\n"+"+"+%7C+
xargs+-I{}+psql+-h+127.0.0.1+-Upostgres+sng+--command+"{}"|code-block|syntax|javascript|1035695|您需要调整模式名称、密码和用户名以匹配您的模式。|1035696|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You can do this with bash also:

<pre><code>#!/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 "{}"
</code></pre>

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

blocks|key|1035938|text|如果可以使用psql，则可以使用\gexec元命令执行查询输出；|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1035939|SELECT
++++format('TRUNCATE+TABLE+%25I.%25I',+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+|code-block|syntax|javascript|1035940|请注意，在版本9.6中引入了\gexec|1035941|entityMap|0|LINK|mutability|MUTABLE|url|https://www.postgresql.org/docs/current/app-psql.html^0|G|6|6|4|0|0|0|E|6|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@$9|X|A|Y|1|Z]]|E|$]]|$1|F|3|G|5|H|7|10|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|11|8|@$9|12|A|13|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]]]

If you can use <a href="https://www.postgresql.org/docs/current/app-psql.html" rel="nofollow noreferrer">psql</a> you can use <code>\gexec</code> meta command to execute query output;

<pre><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 
</code></pre>

Note that <code>\gexec</code> is introduced into the version 9.6

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: 

<pre><code>SELECT 'TRUNCATE TABLE ' || tablename || ';' FROM pg_tables WHERE tableowner='MYUSER';
</code></pre>

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

Truncating all tables in a Postgres database

SQL数据库

PostgreSQL

数据库

在重建之前，我经常需要删除PostgreSQL数据库中的所有数据。如何在SQL中直接执行此操作？目前，我已经设法想出了一条SQL语句，它返回我需要执行的所有命令：SELECT 'TRUNCATE TABLE ' ||  tablename || ';' FROM pg_tables WHERE tableowner='MYUSER';但是一旦我有了它们，我就看不到一种以编程方式执行它们的方法。

问截断Postgres数据库中的所有表
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问截断Postgres数据库中的所有表EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问截断Postgres数据库中的所有表
EN