To list all indexes in a database

When we create a pk in PostgreSQL, an index named table_name_pkey is automatically created.

The following script gives all indexes, those created by the pk and the others.

Don’t forget that PostgreSQL does not create automatically an index when you create a fk. You have to do it yourself !

By the way, this script works for indexes on multiple columns thank the handy function array_to_string that makes a concatenation of the column names. It works because the two first fields selected are given in the order by.

select 
    t.relname as table_name
    i.relname as index_name
    ,array_to_string(array_agg(a.attname), ', ') as column_names
from
     pg_class t
    ,pg_class i
    ,pg_index ix
    ,pg_attribute a
where
    t.oid = ix.indrelid
and 
    i.oid = ix.indexrelid
and 
    a.attrelid = t.oid
and 
    a.attnum = any(ix.indkey)
and 
    t.relkind = 'r' -- takes regular tables
and 
    t.relname not like 'pg_%' -- exclude system catalogs
group by
     t.relname
    ,i.relname
order by
     t.relname
    ,i.relname;