如何在Postgres中删除表的所有索引？

Question

如何在Postgres中删除表的所有索引？

postgresqlindexingmetadatadynamic-sqlddl

22

我一直有这个问题：我需要删除一个表上的20个索引来进行测试。删除该表不会删除所有元数据。

似乎没有通配符drop index ix_table_*或其他有用的命令。看起来可以编写一些围绕psql的bash循环。
肯定有更好的方法！你有什么想法吗？

- Erin

https://dev59.com/ZGw15IYBdhLWcg3wLIzI#6777904 - Dmitry S

当您说“所有索引”时，是否意味着包括通过隐式创建索引实现的约束（UNIQUE、PK、EXCLUDE）？ - Erwin Brandstetter

在这种情况下，只需要索引。 - user

4个回答

11

这是我如何从Postgres中删除所有索引，但排除所有主键。

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;

执行：

SELECT drop_all_indexes();

在实际执行“DROP INDEX xxx”之前，我建议使用“--”将“EXECUTE ...”这行注释掉，并取消注释“RAISE INFO”那行，使用“select func_name（）;”运行它并仔细检查一下，确保自己不会删除不应该删除的东西。

对于我们的应用程序，所有模式语句，包括索引创建，都在一个文件 app.sql 中。在整个项目进入生产之前，我们想清理所有历史创建的索引，然后使用以下方法重新创建它们：

psql -f /path/to/app.sql

希望这有所帮助。

- Emily

这个解决方案对我来说非常完美。非常感谢！我所需要做的就是稍微改变一下选择查询以满足我的要求。RAISE INFO 的想法为我节省了很多时间。 - LingYan Meng

6

下面的查询将删除所有与任何约束条件（主键、唯一键）无关的用户索引。

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

你可以使用 psql 的 \gexec 元命令功能来执行语句。

- Sahap Asci

这里有一个类似的可以用来删除约束的查询语句，希望能帮上忙：

SELECT format ('ALTER TABLE %I.%I DROP CONSTRAINT %I;', nsp.nspname, rel.relname, con.conname)          FROM pg_catalog.pg_constraint con             INNER JOIN pg_catalog.pg_class rel                        ON rel.oid = con.conrelid             INNER JOIN pg_catalog.pg_namespace nsp                        ON nsp.oid = connamespace        WHERE nsp.nspname     = 'cam'        order by  con.conname -- hacky way to ensure FKs come before PKs

- JohnLBevan

-1

为了保持简单，我会考虑官方实现中提到的方法。请参考https://www.postgresql.org/docs/7.2/sql-altertable.html#:~:text=For%20example%2C%20to%20drop%20all%20constraints%20on%20a%20table。

-- store table structure 
-- into temp table 
create temporary table t$ as 
select * from table_name limit 0;
-- drop the table, 
-- 'cascade' ensures it will not 
-- complain about any dependencies
drop table table_name cascade;
-- recreate the original table
create table table_name as 
select * from t$;
-- drop temporary table
drop table t$;

- okharch

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

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

DO
$do$
DECLARE
   _sql text;
BEGIN   
   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 = 'table_name'::regclass  -- possibly schema-qualified
   AND    d.objid IS NULL                      -- no internal dependency
   INTO   _sql;
   
   IF _sql IS NOT NULL THEN                    -- only if index(es) found
     EXECUTE _sql;
   END IF;
END
$do$;

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

官方文档:

DEPENDENCY_INTERNAL (i)

依赖对象是在引用对象创建时作为其内部实现的一部分创建的。

您可以将此内容包装在一个函数中以进行重复执行。
相关:

将表名作为 PostgreSQL 函数参数