postgresql 如何删除 Postgres 中表的所有索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34010401/
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 indexes of a table in Postgres?
提问by Erin
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.
我一直遇到这个问题：我需要删除表上的 20 个索引以进行测试。删除表不会删除所有这些元数据。
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?
似乎没有通配符drop index ix_table_*或任何有用的命令。您可以编写围绕 psql 的一些 bash 循环。
一定有更好的东西！想法？
回答by Erwin Brandstetter
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$$;
Does not touch indexes created as implementation detail of constraints (UNIQUE, PK, EXCLUDE).
The documentation:
不涉及作为约束 ( UNIQUE, PK, EXCLUDE) 的实现细节创建的索引。
文档：
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.
DEPENDENCY_INTERNAL (i)依赖对象是作为被引用对象创建的一部分而创建的，并且实际上只是其内部实现的一部分。
You could wrap this in a function for repeated execution.
Related:
您可以将其包装在一个函数中以重复执行。
有关的：
Aside: This is a misunderstanding:
旁白：这是一个误解：
Dropping the table doesn't drop all of this metadata.
删除表不会删除所有这些元数据。
Dropping a table alwayscascades to all indexes on the table.
删除表总是级联到表上的所有索引。
回答by Emily
This is how I remove all indexes from postgres, excluding all pkey.
这就是我从 postgres 中删除所有索引的方法，不包括所有 pkey。
CREATE OR REPLACE FUNCTION drop_all_indexes() RETURNS INTEGER AS $$
DECLARE
i RECORD;
BEGIN
FOR i IN
(SELECT relname FROM pg_class
-- exclude all pkey, exclude system catalog which starts with 'pg_'
WHERE relkind = 'i' AND relname NOT LIKE '%_pkey%' AND relname NOT LIKE 'pg_%')
LOOP
-- RAISE INFO 'DROPING INDEX: %', i.relname;
EXECUTE 'DROP INDEX ' || i.relname;
END LOOP;
RETURN 1;
END;
$$ LANGUAGE plpgsql;
To execute:
执行：
SELECT drop_all_indexes();
Before actually executing 'DROP INDEX xxx', I would comment out the line 'EXECUTE ...' using '-- ', and uncomment the 'RAISE INFO' line, run it with 'select func_name();' and double check I'm not dropping something I should not.
在实际执行“DROP INDEX xxx”之前，我会使用“--”注释掉“EXECUTE ...”行，并取消注释“RAISE INFO”行，使用“select func_name();”运行它 并仔细检查我没有丢弃我不应该丢弃的东西。
For our application, we have all schema statements including indexes creation in one file app.sql. Before this whole project goes to production, we want to clean up all historically created indexes, then recreate them using:
对于我们的应用程序，我们在一个文件app.sql 中拥有包括索引创建在内的所有架构语句。在整个项目投入生产之前，我们要清理所有历史上创建的索引，然后使用以下方法重新创建它们：
psql -f /path/to/app.sql
Hope this helps.
希望这可以帮助。
回答by Sahap Asci
The query below drops all userindexes which are notrelated with any constraint(primary key, unique key)
下面的查询删除所有用户的这些指标没有任何相关的约束（主键，唯一键）
SELECT
format('DROP INDEX %I.%I;', n.nspname, c_ind.relname)
FROM pg_index ind
JOIN pg_class c_ind ON c_ind.oid = ind.indexrelid
JOIN pg_namespace n ON n.oid = c_ind.relnamespace
LEFT JOIN pg_constraint cons ON cons.conindid = ind.indexrelid
WHERE
n.nspname NOT IN ('pg_catalog','information_schema') AND
n.nspname !~ '^pg_toast'::TEXT AND
cons.oid IS NULL
You can use \gexecmeta-command feature of psql to execute statement
您可以使用\gexecpsql 的元命令功能来执行语句
