SQL 如何使用通配符删除 PostgreSQL 中的多个表

How to drop multiple tables in PostgreSQL using a wildcard

提问by Tom Feiner

When working with partitions, there is often a need to delete all partitions at once.

使用分区时，通常需要一次删除所有分区。

However

然而

DROP TABLE tablename*

Does not work. (The wildcard is not respected).

不起作用。（不遵守通配符）。

Is there an elegant (read: easy to remember) way to drop multiple tables in one command with a wildcard?

是否有一种优雅（阅读：易于记忆）的方法来使用通配符在一个命令中删除多个表？

回答by Frank Heikens

Use a comma separated list:

使用逗号分隔的列表：

DROP TABLE foo, bar, baz;

If you realy need a footgun, this one will do it's job:

如果你真的需要一把脚踏枪，这个就可以了：

CREATE OR REPLACE FUNCTION footgun(IN _schema TEXT, IN _parttionbase TEXT) 
RETURNS void 
LANGUAGE plpgsql
AS
$$
DECLARE
    row     record;
BEGIN
    FOR row IN 
        SELECT
            table_schema,
            table_name
        FROM
            information_schema.tables
        WHERE
            table_type = 'BASE TABLE'
        AND
            table_schema = _schema
        AND
            table_name ILIKE (_parttionbase || '%')
    LOOP
        EXECUTE 'DROP TABLE ' || quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
        RAISE INFO 'Dropped table: %', quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
    END LOOP;
END;
$$;

SELECT footgun('public', 'tablename');

回答by prongs

Here's another hackish answer to this problem. It works in ubuntuand maybe some other os too. do a \dtin postgres command prompt(the command prompt was running inside genome-terminalin my case). Then you'll see a lot of tables in the terminal. Now use ctrl+click-dragfunctionality of the genome-terminalto copy all tables' names. Open python, do some string processing(replace ' ' by '' and then '\n' by ',') and you get comma separated list of all tables. Now in psql shell do a drop table CTRL+SHIFT+Vand you're done. I know it's too specific I just wanted to share. :)

这是对这个问题的另一个骇人听闻的答案。它也适用于ubuntu其他操作系统。\dt在 postgres 命令提示符中做一个（genome-terminal在我的例子中命令提示符在里面运行）。然后你会在终端中看到很多表。现在使用 的ctrl+click-drag功能genome-terminal来复制所有表的名称。打开 python，做一些字符串处理（用 '' 替换 ' ' 然后用 ',' 替换 '\n'），你会得到所有表的逗号分隔列表。现在在 psql shell 中做一个drop table CTRL+SHIFT+V你就完成了。我知道这太具体了，我只是想分享一下。:)

回答by isaaclw

I used this.

我用过这个。

echo "select 'drop table '||tablename||';' from pg_tables where tablename like 'name%'" | \
    psql -U postgres -d dbname -t | \
    psql -U postgres -d dbname

Substitute in appropriate values for dbnameand name%.

将dbname和替换为适当的值name%。

回答by Scott Marlowe

I've always felt way more comfortable creating a sql script I can review and test before I run it than relying on getting the plpgsql just right so it doesn't blow away my database. Something simple in bash that selects the tablenames from the catalog, then creates the drop statements for me. So for 8.4.x you'd get this basic query:

我一直觉得创建一个可以在运行之前检查和测试的 sql 脚本比依靠让 plpgsql 恰到好处更舒服，这样它就不会破坏我的数据库。bash 中的一些简单操作，从目录中选择表名，然后为我创建 drop 语句。所以对于 8.4.x，你会得到这个基本查询：

SELECT 'drop table '||n.nspname ||'.'|| c.relname||';' as "Name" 
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
     AND n.nspname <> 'pg_catalog'
     AND n.nspname <> 'information_schema'
     AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid);

Which you can add a where clause to. (where c.relname ilike 'bubba%')

您可以向其中添加 where 子句。( where c.relname ilike 'bubba%')

Output looks like this:

输出如下所示：

         Name          
-----------------------
 drop table public.a1;
 drop table public.a2;

So, save that to a .sql file and run it with psql -f filename.sql

因此，将其保存到 .sql 文件并使用 psql -f filename.sql 运行它

回答by Campa

Disclosure: this answer is meant for Linux users.

披露：此答案适用于 Linux 用户。

I would add some more specific instructions to what @prongs said:

我会为@prongs 所说的内容添加一些更具体的说明：

• \dtcan support wildcards: so you can run \dt myPrefix*for example, to select only the tables you want to drop;
• after CTRL-SHIFT-DRAGto select then CTRL-SHIFT-Cto copy the text;
• in vim, go to INSERT MODEand paste the tables with CTRL-SHIFT-V;
• press ESC, then run :%s/[ ]*\n/, /gto translate it to comma-separated list, then you can paste it (excluding the last comma) in DROP TABLE % CASCADE.

• \dt可以支持通配符：\dt myPrefix*例如，您可以运行以仅选择要删除的表；
• 之后CTRL-SHIFT-DRAG选择然后CTRL-SHIFT-C复制文本；
• 在 中vim，转到INSERT MODE并粘贴表格CTRL-SHIFT-V；
• 按ESC，然后运行:%s/[ ]*\n/, /g将其转换为逗号分隔的列表，然后您可以将其粘贴（不包括最后一个逗号）DROP TABLE % CASCADE。

回答by Jon

Using linux command line tools, it can be done this way:

使用linux命令行工具，可以这样完成：

psql -d mydb -P tuples_only=1 -c '\dt' | cut -d '|' -f 2 | paste -sd "," | sed 's/ //g' | xargs -I{} echo psql -d mydb -c "drop table {};"

NOTE: The last echo is there because I couldn't find a way to put quotes around the drop command, so you need to copy and paste the output and add the quotes yourself.

注意：最后一个回声在那里，因为我找不到在 drop 命令周围加上引号的方法，所以您需要复制并粘贴输出并自己添加引号。

If anyone can fix that minor issue, that'd be awesome sauce.

如果有人能解决那个小问题，那就太好了。

回答by Ben

So I faced this problem today. I loaded my server db through pgadmin3 and did it that way. Tables are sorted alphabetically so shift and click followed by delete works well.

所以我今天遇到了这个问题。我通过 pgadmin3 加载了我的服务器数据库并这样做了。表格按字母顺序排序，因此 shift 和 click 然后删除效果很好。