如何删除psql（PostgreSQL交互式终端）中以常用词开头的所有表？ [英] How do I drop all tables in psql (PostgreSQL interactive terminal) that starts with a common word?

问题描述

如何删除名称以 doors _ 开头的所有表？我可以使用 drop table 命令来做某种正则表达式吗？

How do I drop all tables whose name start with, say, doors_? Can I do some sort of regex using the drop table command?

我更喜欢不编写自定义脚本，而是全部解决方案受到欢迎。

I prefer not writing a custom script but all solutions are welcomed. Thanks!

推荐答案

此脚本将生成DDL命令以将其全部删除：

This script will generate the DDL commands to drop them all:

SELECT 'DROP TABLE ' || t.oid::regclass || ';'
FROM   pg_class t
-- JOIN   pg_namespace n ON n.oid = t.relnamespace -- to select by schema
WHERE  t.relkind = 'r'
AND    t.relname ~~ E'doors\_%' -- enter search term for table here
-- AND n.nspname ~~ '%myschema%' -- optionally select by schema(s), too
ORDER  BY 1;

强制转换 t.oid :: regclass 使语法也适用于混合大小写标识符，保留字或表名中的特殊字符。它还可以防止SQL注入，并在必要时添加模式名称。有关手册中对象标识符类型的详细信息。

The cast t.oid::regclass makes the syntax work for mixed case identifiers, reserved words or special characters in table names, too. It also prevents SQL injection and prepends the schema name where necessary. More about object identifier types in the manual.

关于模式搜索路径。

您也可以自动执行删除操作，但这是不明智的，不检查您实际删除的内容

You could automate the dropping, too, but it's unwise not to check what you actually delete before you do.

您可以附加 CASCADE 到依赖于对象（视图和引用外键）的每个DROP语句。但是，除非您非常了解自己在做什么，否则这是不明智的。外键约束不会造成很大的损失，但这也将完全删除所有从属视图。如果没有 CASCADE ，您会收到错误消息，通知您哪些对象阻止您删除表。然后您可以处理它。

You could append CASCADE to every statement to DROP depending objects (views and referencing foreign keys). But, again, that's unwise unless you know very well what you are doing. Foreign key constraints are no big loss, but this will also drop all dependent views entirely. Without CASCADE you get error messages informing you which objects prevent you from dropping the table. And you can then deal with it.

这篇关于如何删除psql（PostgreSQL交互式终端）中以常用词开头的所有表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！