postgresql: how list indexed columns?

Question

There is a lot of information, that can be retrieved from information_schema and pg_catalog in postgresql. I would like to retrieve information about columns indexed by a certain index, similar to what I would achieve with pragma index_info(<index_name>) in sqlite3. How can this be achieved without parsing create index statement?

Answer 1

Those things are pretty easy to find out.

Simply run psql with the -E option and it will show you the SQL statements that are used. So when running \d index_name the following statement (among others) is used to retrieve the index columns:

SELECT a.attname,
       pg_catalog.format_type (a.atttypid,a.atttypmod),
       (SELECT SUBSTRING (pg_catalog.pg_get_expr (d.adbin,d.adrelid) FOR 128)
        FROM pg_catalog.pg_attrdef d
        WHERE d.adrelid = a.attrelid
        AND   d.adnum = a.attnum
        AND   a.atthasdef),
       a.attnotnull,
       a.attnum,
       pg_catalog.pg_get_indexdef (a.attrelid,a.attnum,TRUE) AS indexdef
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = (SELECT oid FROM pg_class WHERE relname = 'index_name')
AND   a.attnum > 0
AND   NOT a.attisdropped
ORDER BY a.attnum;