PostgreSQL如何查询已有索引名称并使用DROP INDEX删除索引

前置说明 #

你不能直接把查询索引的SELECT语句替换成DROP INDEX执行，DDL语句无法直接读取查询结果作为操作对象，你可以按照下面的步骤操作，既安全又能满足需求： #

步骤1：查询指定表的所有索引信息 #

执行下面的语句可以筛选出目标表关联的所有索引，包含索引名、定义等信息：

SELECT 
  schemaname, -- 索引所属模式
  tablename,  -- 索引关联的表名
  indexname,  -- 索引名，删除时需要用到
  indexdef    -- 索引的创建语句，可用来核对索引用途
FROM pg_indexes
WHERE 
  tablename = '替换为你的目标表名'
  -- 表在默认public模式的话可以保留，非默认请替换为对应模式名
  AND schemaname = 'public';

步骤2：删除索引 #

情况1：仅删除单个/少量索引 #

拿到第一步查到的indexname字段值后，直接执行DROP INDEX语句即可，推荐加上IF EXISTS避免索引不存在时报错：

-- 通用写法，加上模式前缀避免重名冲突
DROP INDEX IF EXISTS 模式名.索引名;

-- 示例：删除public模式下名为users_phone_idx的索引
DROP INDEX IF EXISTS public.users_phone_idx;

情况2：批量删除目标表的多个索引 #

如果需要删除的索引数量多，可通过下面的语句自动生成所有删除命令，方便你核对后批量执行：

SELECT 
  'DROP INDEX IF EXISTS ' || quote_ident(schemaname) || '.' || quote_ident(indexname) || ';' 
AS drop_command
FROM pg_indexes
WHERE 
  tablename = '替换为你的目标表名'
  AND schemaname = 'public'
  -- 可选过滤：不删除主键对应的索引，避免误删约束
  AND indexname NOT LIKE '%_pkey';

执行上述查询后，会返回一列完整的DROP INDEX语句，你逐行核对确认是需要删除的索引后，复制出来直接执行即可。 #

注意：主键、唯一约束、外键约束对应的系统自动生成索引不要直接删除，这类索引和约束绑定，直接删除会导致约束失效，若要删除对应约束请使用ALTER TABLE 表名 DROP CONSTRAINT 约束名语句操作。

内容的提问来源于stack exchange，提问作者SteamedBun