How can I drop all indexes of a table in Postgres?

Question

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?

Answer 1

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:

• Table name as a PostgreSQL function parameter

---

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.