如何从命令行删除PostgreSQL中的所有表？
我不想删除数据库本身，只想删除其中的所有表和所有数据。
当前回答
使用psql的基于终端的方法最适合我。我甚至创建了一个bash函数，因为它便于开发：
psqlDropTables() {
PGPASSWORD=<your password>
PGTABLE=<your table name>
PGUSER=<your pg user name>
PGPASSWORD=$PGPASSWORD psql -ah 127.0.0.1 $PGTABLE $PGUSER -c "
SELECT
'DROP TABLE IF EXISTS \"' || tablename || '\" CASCADE;' from
pg_tables WHERE schemaname = 'public';" | grep DROP | awk 'NR>1{print $0}' | sed "s/\"/'/g" | PGPASSWORD=$PGPASSWORD xargs -i psql -ah 127.0.0.1 $PGTABLE $PGUSER -c {}
}
它创建了此响应中所述的所有必需的放置表语句，将“替换为”并在DB上运行它们。
其他回答
嗯，因为我喜欢从命令行工作。。。
psql -U <user> -d <mydb> -c '\dt' | cut -d ' ' -f 4 | sed -e "s/^/drop table if exists /" | sed -e "s/$/;/"
-c'\dt'将调用list tables命令。
List of relations
Schema | Name | Type | Owner
--------+-------------------+-------+----------
public | _d_psidxddlparm | table | djuser
public | _d_psindexdefn | table | djuser
现在剪切-d“”-f 4，通过管道将其输出抓取第4个字段（当使用空格作为分隔符时），即表。
然后，sed被用于给一个drop表加前缀，并在后面加后缀；命令分隔符。
|egrep“_d_”-再将其转换为grep，这样您就可以更有选择地删除哪些表。
drop table if exists _d_psidxddlparm;
drop table if exists _d_psindexdefn;
注意：如前所述，这将为\dt命令输出的列标题和末尾的总行生成伪行。我会用grepping来避免，但你可以用head和tail。
只需执行以下查询：
DO $$ DECLARE
r RECORD;
BEGIN
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 $$;
使用psql的基于终端的方法最适合我。我甚至创建了一个bash函数，因为它便于开发：
psqlDropTables() {
PGPASSWORD=<your password>
PGTABLE=<your table name>
PGUSER=<your pg user name>
PGPASSWORD=$PGPASSWORD psql -ah 127.0.0.1 $PGTABLE $PGUSER -c "
SELECT
'DROP TABLE IF EXISTS \"' || tablename || '\" CASCADE;' from
pg_tables WHERE schemaname = 'public';" | grep DROP | awk 'NR>1{print $0}' | sed "s/\"/'/g" | PGPASSWORD=$PGPASSWORD xargs -i psql -ah 127.0.0.1 $PGTABLE $PGUSER -c {}
}
它创建了此响应中所述的所有必需的放置表语句，将“替换为”并在DB上运行它们。
以防万一。。。清理Postgresql数据库的简单Python脚本
import psycopg2
import sys
# Drop all tables from a given database
try:
conn = psycopg2.connect("dbname='akcja_miasto' user='postgres' password='postgres'")
conn.set_isolation_level(0)
except:
print "Unable to connect to the database."
cur = conn.cursor()
try:
cur.execute("SELECT table_schema,table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_schema,table_name")
rows = cur.fetchall()
for row in rows:
print "dropping table: ", row[1]
cur.execute("drop table " + row[1] + " cascade")
cur.close()
conn.close()
except:
print "Error: ", sys.exc_info()[1]
确保复制后缩进正确，因为Python依赖它。
注意：我的答案是真正删除表和其他数据库对象；为了删除表中的所有数据，即截断所有表，Endre Both在一个月后提供了一个同样执行良好的（直接执行）语句。
对于不能仅删除SCHEMA public CASCADE；的情况；，由当前用户拥有；或者，这是我编写的一个独立的SQL脚本，它是事务安全的（即，您可以将它放在BEGIN和ROLLBACK之间；只测试它或COMMIT；实际执行操作），并清理“所有”数据库对象……好吧，我们的应用程序使用的或我可以明智地添加的数据库中使用的所有对象，即：
表上的触发器表上的约束（FK、PK、CHECK、UNIQUE）标记ēs视图（正常或材料化）桌子序列例程（聚合函数、函数、过程）所有nōn默认（即非公共或DB内部）模式“我们”拥有：当作为“非数据库超级用户”运行时，脚本很有用；超级用户可以删除所有模式（但真正重要的模式仍然被明确排除）扩展名（用户提供，但我通常会故意将其保留在其中）
未删除的是（有些是故意的；有些只是因为我在DB中没有示例）：
公共模式（例如，扩展提供的内容）排序规则和其他区域设置内容事件触发器文本搜索内容，…（请参阅此处了解我可能错过的其他内容）角色或其他安全设置复合类型烤面包桌FDW和外来表格
当您要还原的转储是与您要还原到的数据库不同的数据库模式版本（例如Debian dbconfig common、Flyway或Liquibase/DB Manul）时，这非常有用。
我还有一个版本，它删除了“除了两个表和属于它们的内容之外的所有内容”（一个序列，手动测试，抱歉，我知道，很无聊），以防有人感兴趣；差异很小。如果感兴趣，请联系我或查看此回购。
SQL
-- Copyright © 2019, 2020
-- mirabilos <t.glaser@tarent.de>
--
-- Provided that these terms and disclaimer and all copyright notices
-- are retained or reproduced in an accompanying document, permission
-- is granted to deal in this work without restriction, including un‐
-- limited rights to use, publicly perform, distribute, sell, modify,
-- merge, give away, or sublicence.
--
-- This work is provided “AS IS” and WITHOUT WARRANTY of any kind, to
-- the utmost extent permitted by applicable law, neither express nor
-- implied; without malicious intent or gross negligence. In no event
-- may a licensor, author or contributor be held liable for indirect,
-- direct, other damage, loss, or other issues arising in any way out
-- of dealing in the work, even if advised of the possibility of such
-- damage or existence of a defect, except proven that it results out
-- of said person’s immediate fault when using the work as intended.
-- -
-- Drop everything from the PostgreSQL database.
DO $$
DECLARE
q TEXT;
r RECORD;
BEGIN
-- triggers
FOR r IN (SELECT pns.nspname, pc.relname, pt.tgname
FROM pg_catalog.pg_trigger pt, pg_catalog.pg_class pc, pg_catalog.pg_namespace pns
WHERE pns.oid=pc.relnamespace AND pc.oid=pt.tgrelid
AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
AND pt.tgisinternal=false
) LOOP
EXECUTE format('DROP TRIGGER %I ON %I.%I;',
r.tgname, r.nspname, r.relname);
END LOOP;
-- constraints #1: foreign key
FOR r IN (SELECT pns.nspname, pc.relname, pcon.conname
FROM pg_catalog.pg_constraint pcon, pg_catalog.pg_class pc, pg_catalog.pg_namespace pns
WHERE pns.oid=pc.relnamespace AND pc.oid=pcon.conrelid
AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
AND pcon.contype='f'
) LOOP
EXECUTE format('ALTER TABLE ONLY %I.%I DROP CONSTRAINT %I;',
r.nspname, r.relname, r.conname);
END LOOP;
-- constraints #2: the rest
FOR r IN (SELECT pns.nspname, pc.relname, pcon.conname
FROM pg_catalog.pg_constraint pcon, pg_catalog.pg_class pc, pg_catalog.pg_namespace pns
WHERE pns.oid=pc.relnamespace AND pc.oid=pcon.conrelid
AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
AND pcon.contype<>'f'
) LOOP
EXECUTE format('ALTER TABLE ONLY %I.%I DROP CONSTRAINT %I;',
r.nspname, r.relname, r.conname);
END LOOP;
-- indicēs
FOR r IN (SELECT pns.nspname, pc.relname
FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pns
WHERE pns.oid=pc.relnamespace
AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
AND pc.relkind='i'
) LOOP
EXECUTE format('DROP INDEX %I.%I;',
r.nspname, r.relname);
END LOOP;
-- normal and materialised views
FOR r IN (SELECT pns.nspname, pc.relname
FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pns
WHERE pns.oid=pc.relnamespace
AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
AND pc.relkind IN ('v', 'm')
) LOOP
EXECUTE format('DROP VIEW %I.%I;',
r.nspname, r.relname);
END LOOP;
-- tables
FOR r IN (SELECT pns.nspname, pc.relname
FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pns
WHERE pns.oid=pc.relnamespace
AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
AND pc.relkind='r'
) LOOP
EXECUTE format('DROP TABLE %I.%I;',
r.nspname, r.relname);
END LOOP;
-- sequences
FOR r IN (SELECT pns.nspname, pc.relname
FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pns
WHERE pns.oid=pc.relnamespace
AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
AND pc.relkind='S'
) LOOP
EXECUTE format('DROP SEQUENCE %I.%I;',
r.nspname, r.relname);
END LOOP;
-- extensions (only if necessary; keep them normally)
FOR r IN (SELECT pns.nspname, pe.extname
FROM pg_catalog.pg_extension pe, pg_catalog.pg_namespace pns
WHERE pns.oid=pe.extnamespace
AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
) LOOP
EXECUTE format('DROP EXTENSION %I;', r.extname);
END LOOP;
-- aggregate functions first (because they depend on other functions)
FOR r IN (SELECT pns.nspname, pp.proname, pp.oid
FROM pg_catalog.pg_proc pp, pg_catalog.pg_namespace pns, pg_catalog.pg_aggregate pagg
WHERE pns.oid=pp.pronamespace
AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
AND pagg.aggfnoid=pp.oid
) LOOP
EXECUTE format('DROP AGGREGATE %I.%I(%s);',
r.nspname, r.proname,
pg_get_function_identity_arguments(r.oid));
END LOOP;
-- routines (functions, aggregate functions, procedures, window functions)
IF EXISTS (SELECT * FROM pg_catalog.pg_attribute
WHERE attrelid='pg_catalog.pg_proc'::regclass
AND attname='prokind' -- PostgreSQL 11+
) THEN
q := 'CASE pp.prokind
WHEN ''p'' THEN ''PROCEDURE''
WHEN ''a'' THEN ''AGGREGATE''
ELSE ''FUNCTION''
END';
ELSIF EXISTS (SELECT * FROM pg_catalog.pg_attribute
WHERE attrelid='pg_catalog.pg_proc'::regclass
AND attname='proisagg' -- PostgreSQL ≤10
) THEN
q := 'CASE pp.proisagg
WHEN true THEN ''AGGREGATE''
ELSE ''FUNCTION''
END';
ELSE
q := '''FUNCTION''';
END IF;
FOR r IN EXECUTE 'SELECT pns.nspname, pp.proname, pp.oid, ' || q || ' AS pt
FROM pg_catalog.pg_proc pp, pg_catalog.pg_namespace pns
WHERE pns.oid=pp.pronamespace
AND pns.nspname NOT IN (''information_schema'', ''pg_catalog'', ''pg_toast'')
' LOOP
EXECUTE format('DROP %s %I.%I(%s);', r.pt,
r.nspname, r.proname,
pg_get_function_identity_arguments(r.oid));
END LOOP;
-- nōn-default schemata we own; assume to be run by a not-superuser
FOR r IN (SELECT pns.nspname
FROM pg_catalog.pg_namespace pns, pg_catalog.pg_roles pr
WHERE pr.oid=pns.nspowner
AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast', 'public')
AND pr.rolname=current_user
) LOOP
EXECUTE format('DROP SCHEMA %I;', r.nspname);
END LOOP;
-- voilà
RAISE NOTICE 'Database cleared!';
END; $$;
在PostgreSQL 9.6（jessie backports）上进行了测试，但后来的添加（由Clément Prévost提供的扩展）除外。9.6和12.2中测试了骨料去除，12.2中也测试了程序去除。欢迎修复错误和进一步改进！
推荐文章
- 查询JSON类型内的数组元素
- 获得PostgreSQL数据库中当前连接数的正确查询
- 纬度和经度的数据类型是什么?
- 如何在PostgreSQL中临时禁用触发器?
- 输入文件似乎是一个文本格式转储。请使用psql
- 使用LIMIT/OFFSET运行查询，还可以获得总行数
- 当恢复sql时，psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何添加列，如果不存在PostgreSQL?
- 如何在Postgres中获得两个字段的MIN() ?
- 截断Postgres数据库中的所有表
- 如何连接列在Postgres选择?
- 将varchar字段的类型更改为整数:"不能自动转换为整数类型"
- PostgreSQL可以索引数组列吗?
- PostgreSQL:角色不允许登录
