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

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 referencedobject, 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.

This is how I remove all indexes from postgres, excluding all pkey.

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;

To execute:

SELECT drop_all_indexes();

Before actually executing 'DROP INDEX xxx', I would comment out the line 'EXECUTE ...' using '-- ', and uncomment the 'RAISE INFO' line, run it with 'select func_name();' and double check I'm not dropping something I should not.

For our application, we have all schema statements including indexes creation in one file app.sql. Before this whole project goes to production, we want to clean up all historically created indexes, then recreate them using:

psql -f /path/to/app.sql

Hope this helps.

The query below drops all user indexes which are not related with any constraint (primary key, unique key)

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

You can use \gexec meta-command feature of psql to execute statement