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

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

Related:

• DROP FUNCTION without knowing the number/type of parameters?

---

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.