如何删除Postgres中表的所有索引？ [英] How can I drop all indexes of a table in Postgres?

问题描述

我一直遇到这个问题：我在桌面上有20个索引，我需要删除才能进行测试。删除表不会丢弃所有这些元数据。

I keep having this problem: I have like 20 indexes on a table that I need to drop in order to do testing. Dropping the table doesn't drop all of this metadata.

似乎没有通配符删除索引ix_table _ * 或任何有用的命令。似乎有一些你可以编写的psql周围的bash循环。

必须有更好的东西！想法？

There doesn't seem to be a wildcard drop index ix_table_* or any useful command. There seem to be some bash loops around psql you can write.
There must be something better! Thoughts?

推荐答案

假设您只想删除普通索引：

Assuming you only want to drop plain indexes:

DO
$$BEGIN
   EXECUTE (
   SELECT 'DROP INDEX ' || string_agg(indexrelid::regclass::text, ', ')
   FROM   pg_index  i
   LEFT   JOIN pg_depend d ON d.objid = i.indexrelid
                          AND d.deptype = 'i'
   WHERE  i.indrelid = 'your_table_name_here'::regclass  -- possibly schema-qualified
   AND    d.objid IS NULL                                -- no internal dependency
   );
END$$;

不接触作为约束的实现细节创建的索引（ UNIQUE ， PK ， EXCLUDE ）。

文档：

Does not touch indexes created as implementation detail of constraints (UNIQUE, PK, EXCLUDE).
The documentation:

DEPENDENCY_INTERNAL (i)

依赖对象是作为创建引用的
对象的一部分创建的，实际上只是其内部实现的一部分。

The dependent object was created as part of creation of the referenced object, and is really just a part of its internal implementation.

你可以将它包装在一个函数中以便重复执行。

相关：

You could wrap this in a function for repeated execution.
Related:

• 表名作为PostgreSQL函数参数

• Table name as a PostgreSQL function parameter

除此之外：这是一个误解：

Aside: This is a misunderstanding:

掉落t他没有丢弃所有这些元数据。

Dropping the table doesn't drop all of this metadata.

删除表总是级联到所有索引表格。

Dropping a table always cascades to all indexes on the table.

这篇关于如何删除Postgres中表的所有索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！