pgsql 删除创建索引_如何删除Postgres中表的所有索引？

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.

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$$;

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:

Aside: This is a misunderstanding:

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

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