To list all the indexes

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’s 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 composite indexes thanks to the handy function array_to_string that creates of comma separated list of the column names. It works because the two first columns in the select clause are given in the order by.

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

By the way, when I was working on the script, I was listening one of the last masterworks of the Johann Sebastian Bach of our time : Pulse of Steve Reich. The music of Steve Reich is a good music to listen when you write programs because this music is well structured and transmits lofty feelings…

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s