List columns with indexes in PostgreSQL

Create some test data...

create table test (a int, b int, c int, constraint pk_test primary key(a, b));create table test2 (a int, b int, c int, constraint uk_test2 unique (b, c));create table test3 (a int, b int, c int, constraint uk_test3b unique (b), constraint uk_test3c unique (c),constraint uk_test3ab unique (a, b));

List indexes and columns indexed:

select    t.relname as table_name,    i.relname as index_name,    a.attname as column_namefrom    pg_class t,    pg_class i,    pg_index ix,    pg_attribute awhere    t.oid = ix.indrelid    and i.oid = ix.indexrelid    and a.attrelid = t.oid    and a.attnum = ANY(ix.indkey)    and t.relkind = 'r'    and t.relname like 'test%'order by    t.relname,    i.relname; table_name | index_name | column_name------------+------------+------------- test       | pk_test    | a test       | pk_test    | b test2      | uk_test2   | b test2      | uk_test2   | c test3      | uk_test3ab | a test3      | uk_test3ab | b test3      | uk_test3b  | b test3      | uk_test3c  | c

Roll up the column names:

select    t.relname as table_name,    i.relname as index_name,    array_to_string(array_agg(a.attname), ', ') as column_namesfrom    pg_class t,    pg_class i,    pg_index ix,    pg_attribute awhere    t.oid = ix.indrelid    and i.oid = ix.indexrelid    and a.attrelid = t.oid    and a.attnum = ANY(ix.indkey)    and t.relkind = 'r'    and t.relname like 'test%'group by    t.relname,    i.relnameorder by    t.relname,    i.relname; table_name | index_name | column_names------------+------------+-------------- test       | pk_test    | a, b test2      | uk_test2   | b, c test3      | uk_test3ab | a, b test3      | uk_test3b  | b test3      | uk_test3c  | c

PostgreSQL (pg_indexes):

SELECT * FROM pg_indexes WHERE tablename = 'mytable';

MySQL (SHOW INDEX):

SHOW INDEX FROM mytable;

\d table_name shows this information from psql, but if you want to get such information from database using SQL then have a look at Extracting META information from PostgreSQL.

I use such info in my utility to report some info from db schema to compare PostgreSQL databases in test and production environments.