如何删除 PostgreSQL 数据库中的所有表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3327312/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I drop all the tables in a PostgreSQL database?
提问by AP257
How can I drop all tables in PostgreSQL, working from the command line?
如何从命令行删除 PostgreSQL 中的所有表？
I don'twant to drop the database itself, just all tables and all the data in them.
我不希望删除数据库本身，只是所有的表格，并在他们所有的数据。
回答by Derek Slife
If all of your tables are in a single schema, this approach could work (below code assumes that the name of your schema is public)
如果您的所有表都在一个模式中，则这种方法可以工作（下面的代码假设您的模式名称是public）
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
If you are using PostgreSQL 9.3 or greater, you may also need to restore the default grants.
如果您使用的是 PostgreSQL 9.3 或更高版本，您可能还需要恢复默认授权。
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
回答by Pablo Santa Cruz
You can write a query to generate a SQL script like this:
您可以编写一个查询来生成这样的 SQL 脚本：
select 'drop table "' || tablename || '" cascade;' from pg_tables;
Or:
或者：
select 'drop table if exists "' || tablename || '" cascade;' from pg_tables;
In case some tables are automatically dropped due to cascade option in a previous sentence.
如果某些表由于前一句中的级联选项而自动删除。
Additionally, as stated in the comments, you might want to filter the tables you want to drop by schema name:
此外，如评论中所述，您可能希望按架构名称过滤要删除的表：
select 'drop table if exists "' || tablename || '" cascade;'
from pg_tables
where schemaname = 'public'; -- or any other schema
And then run it.
然后运行它。
Glorious COPY+PASTE will also work.
Glorious COPY+PASTE 也可以。
回答by User
The most accepted answer as of this writing (January 2014) is:
在撰写本文时（2014 年 1 月），最被接受的答案是：
drop schema public cascade;
create schema public;
This does work, however if your intention is to restore the public schema to its virgin state this does not fully accomplish the task. Under pgAdmin III for PostgreSQL 9.3.1, if you click on the "public" schema created this way and look in the "SQL pane" you will see the following:
这确实有效，但是如果您打算将公共架构恢复到其原始状态，这并不能完全完成任务。在 PostgreSQL 9.3.1 的 pgAdmin III 下，如果您单击以这种方式创建的“公共”模式并查看“SQL 窗格”，您将看到以下内容：
-- Schema: public
-- DROP SCHEMA public;
CREATE SCHEMA public
AUTHORIZATION postgres;
However, by contrast a brand new database will have the following:
但是，相比之下，全新的数据库将具有以下内容：
-- Schema: public
-- DROP SCHEMA public;
CREATE SCHEMA public
AUTHORIZATION postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
COMMENT ON SCHEMA public
IS 'standard public schema';
For me using a python web framework which creates database tables (web2py), using the former caused problems:
对于我使用创建数据库表（web2py）的python web框架，使用前者会导致问题：
<class 'psycopg2.ProgrammingError'> no schema has been selected to create in
So to my mind the fully correct answer is:
所以在我看来，完全正确的答案是：
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
COMMENT ON SCHEMA public IS 'standard public schema';
Also note to issue these commands in pgAdmin III, I used the Query tool ( magnifying glass icon "Execute abritrary SQL queries") or you could use Plugins-> PSQL Console
还要注意在 pgAdmin III 中发出这些命令，我使用了查询工具（放大镜图标“执行 abritrary SQL 查询”）或者您可以使用插件-> PSQL 控制台
Note
笔记
If you have any extensions installed they will be dropped when you drop the schema, so you should make note of what you need installed and then execute statements as necessary. E.g.
如果您安装了任何扩展，当您删除架构时，它们将被删除，因此您应该记下您需要安装的内容，然后根据需要执行语句。例如
CREATE EXTENSION postgis;
CREATE EXTENSION postgis;
回答by Piotr Findeisen
You can drop all tables with
您可以删除所有表
DO $$ DECLARE
r RECORD;
BEGIN
-- if the schema you operate on is not "current", you will want to
-- replace current_schema() in query with 'schematodeletetablesfrom'
-- *and* update the generate 'DROP...' accordingly.
FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
END LOOP;
END $$;
IMO this is better than drop schema public, because you don't need to recreate the schemaand restore all the grants.
IMO 这比 好drop schema public，因为您不需要重新创建schema和恢复所有赠款。
Additional bonus that this doesn't require external scripting language, nor copy-pasting of generated SQL back to the interpreter.
额外的好处是这不需要外部脚本语言，也不需要将生成的 SQL 复制粘贴回解释器。
回答by a_horse_with_no_name
If everything you want to drop is ownedby the same user, then you can use:
如果您要删除的所有内容都归同一用户所有，则可以使用：
drop owned by the_user;
This will drop everythingthat the user owns.
这将降低所有用户拥有。
That includes materialized views, views, sequences, triggers, schemas, functions, types, aggregates, operators, domains and so on (so, really: everything) that the_userowns (=created).
这包括物化视图、视图、序列、触发器、模式、函数、类型、聚合、运算符、域等（所以，实际上：所有）the_user（=创建）。
You have to replace the_userwith the actual username, currently there is no option to drop everything for "the current user". The upcoming 9.5 version will have the option drop owned by current_user.
您必须the_user用实际用户名替换，目前没有选项可以删除“当前用户”的所有内容。即将推出的 9.5 版本将提供该选项drop owned by current_user。
More details in the manual: http://www.postgresql.org/docs/current/static/sql-drop-owned.html
手册中的更多详细信息：http: //www.postgresql.org/docs/current/static/sql-drop-owned.html
回答by LenW
As per Pablo above, to just drop from a specific schema, with respect to case:
根据上面的 Pablo，就案例而言，只是从特定模式中删除：
select 'drop table "' || tablename || '" cascade;'
from pg_tables where schemaname = 'public';
回答by Joe Van Dyk
drop schema public cascade;
should do the trick.
应该做的伎俩。
回答by Tim Diggins
Following Pablo and LenW, here's a one-liner that does it all both preparing and then executing:
继 Pablo 和 LenW 之后，这是一个单行代码，它完成了准备和执行的所有工作：
psql -U $PGUSER $PGDB -t -c "select 'drop table \"' || tablename || '\" cascade;' from pg_tables where schemaname = 'public'" | psql -U $PGUSER $PGDB
psql -U $PGUSER $PGDB -t -c "select 'drop table \"' || tablename || '\" cascade;' from pg_tables where schemaname = 'public'" | psql -U $PGUSER $PGDB
NB: either set or replace $PGUSERand $PGDBwith the values you want
注意：设置或替换$PGUSER并$PGDB使用您想要的值
回答by Mark Lawrence
If you have the PL/PGSQL procedural language installedyou can use the following to remove everything without a shell/Perl external script.
如果您有PL / PGSQL过程语言安装，你可以使用下面的无壳去除一切/ Perl的外部脚本。
DROP FUNCTION IF EXISTS remove_all();
CREATE FUNCTION remove_all() RETURNS void AS $$
DECLARE
rec RECORD;
cmd text;
BEGIN
cmd := '';
FOR rec IN SELECT
'DROP SEQUENCE ' || quote_ident(n.nspname) || '.'
|| quote_ident(c.relname) || ' CASCADE;' AS name
FROM
pg_catalog.pg_class AS c
LEFT JOIN
pg_catalog.pg_namespace AS n
ON
n.oid = c.relnamespace
WHERE
relkind = 'S' AND
n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
pg_catalog.pg_table_is_visible(c.oid)
LOOP
cmd := cmd || rec.name;
END LOOP;
FOR rec IN SELECT
'DROP TABLE ' || quote_ident(n.nspname) || '.'
|| quote_ident(c.relname) || ' CASCADE;' AS name
FROM
pg_catalog.pg_class AS c
LEFT JOIN
pg_catalog.pg_namespace AS n
ON
n.oid = c.relnamespace WHERE relkind = 'r' AND
n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
pg_catalog.pg_table_is_visible(c.oid)
LOOP
cmd := cmd || rec.name;
END LOOP;
FOR rec IN SELECT
'DROP FUNCTION ' || quote_ident(ns.nspname) || '.'
|| quote_ident(proname) || '(' || oidvectortypes(proargtypes)
|| ');' AS name
FROM
pg_proc
INNER JOIN
pg_namespace ns
ON
(pg_proc.pronamespace = ns.oid)
WHERE
ns.nspname =
'public'
ORDER BY
proname
LOOP
cmd := cmd || rec.name;
END LOOP;
EXECUTE cmd;
RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT remove_all();
Rather than type this in at the "psql" prompt I would suggest you copy it to a file and then pass the file as input to psql using the "--file" or "-f" options:
与其在“psql”提示符下输入，我建议您将其复制到一个文件中，然后使用“--file”或“-f”选项将该文件作为输入传递给 psql：
psql -f clean_all_pg.sql
Credit where credit is due: I wrote the function, but think the queries (or the first one at least) came from someone on one of the pgsql mailing lists years ago. Don't remember exactly when or which one.
信用应得的地方：我编写了该函数，但认为查询（或至少第一个）来自几年前 pgsql 邮件列表中的某个人。不记得确切的时间或哪一个。
回答by Endre Both
If you want to nuke all tables anyway, you can dispense with niceties such as CASCADE by putting all tables into a single statement. This also makes execution quicker.
如果您无论如何都想取消所有表，则可以通过将所有表放入单个语句来省去诸如 CASCADE 之类的细节。这也使执行速度更快。
SELECT 'TRUNCATE TABLE ' || string_agg('"' || tablename || '"', ', ') || ';'
FROM pg_tables WHERE schemaname = 'public';
Executing it directly:
直接执行：
DO $$
DECLARE tablenames text;
BEGIN
tablenames := string_agg('"' || tablename || '"', ', ')
FROM pg_tables WHERE schemaname = 'public';
EXECUTE 'TRUNCATE TABLE ' || tablenames;
END; $$
Replace TRUNCATEwith DROPas applicable.
更换TRUNCATE与DROP适用。
