This command works for non-partitioned tables:

select * from pg_indexes

But for partitioned tables, it does not list the original index, but the indexes that postgresql created for each partition.

To drop an index, I need the original name.

How do I list all indexes on a table that is partitioned (with the name it was created)?


1 Answer 1



create or replace view vw_list_indexes
    select i.relname as indexname, t.relname as tablename, t.OID, idx.indisprimary
    from pg_class i
    join pg_index idx on idx.indexrelid = i.oid
    join pg_class t on t.oid = idx.indrelid
    where i.relkind = 'I';

select * from vw_list_indexes where oid = 'schema.table'::regclass;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.