List all index names, column names and its table name of a PostgreSQL database

Question

What is the query to get the list all index names, its column name and its table name of a postgresql database?

I have tried to get the list of all indexes in a db by using this query but how to get the list of indexes, its column names and its table names?

 SELECT *  FROM pg_class, pg_index  WHERE pg_class.oid = pg_index.indexrelid  AND pg_class.oid IN (      SELECT indexrelid      FROM pg_index, pg_class      WHERE pg_class.oid=pg_index.indrelid      AND indisunique != 't'      AND indisprimary != 't'      AND relname !~ '^pg_'); 

Answer 1

This will output all indexes with details (extracted from my view definitions):

SELECT i.relname as indname,        i.relowner as indowner,        idx.indrelid::regclass,        am.amname as indam,        idx.indkey,        ARRAY(        SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)        FROM generate_subscripts(idx.indkey, 1) as k        ORDER BY k        ) as indkey_names,        idx.indexprs IS NOT NULL as indexprs,        idx.indpred IS NOT NULL as indpred FROM   pg_index as idx JOIN   pg_class as i ON     i.oid = idx.indexrelid JOIN   pg_am as am ON     i.relam = am.oid; 

Optionally add an extra join to the end so as to trim the namespaces:

SELECT i.relname as indname,        i.relowner as indowner,        idx.indrelid::regclass,        am.amname as indam,        idx.indkey,        ARRAY(        SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)        FROM generate_subscripts(idx.indkey, 1) as k        ORDER BY k        ) as indkey_names,        idx.indexprs IS NOT NULL as indexprs,        idx.indpred IS NOT NULL as indpred FROM   pg_index as idx JOIN   pg_class as i ON     i.oid = idx.indexrelid JOIN   pg_am as am ON     i.relam = am.oid JOIN   pg_namespace as ns ON     ns.oid = i.relnamespace AND    ns.nspname = ANY(current_schemas(false)); 

Answer 2

More human friendly version of @Denis solution:

SELECT   U.usename                AS user_name,   ns.nspname               AS schema_name,   idx.indrelid :: REGCLASS AS table_name,   i.relname                AS index_name,   idx.indisunique          AS is_unique,   idx.indisprimary         AS is_primary,   am.amname                AS index_type,   idx.indkey,        ARRAY(            SELECT pg_get_indexdef(idx.indexrelid, k + 1, TRUE)            FROM              generate_subscripts(idx.indkey, 1) AS k            ORDER BY k        ) AS index_keys,   (idx.indexprs IS NOT NULL) OR (idx.indkey::int[] @> array[0]) AS is_functional,   idx.indpred IS NOT NULL AS is_partial FROM pg_index AS idx   JOIN pg_class AS i     ON i.oid = idx.indexrelid   JOIN pg_am AS am     ON i.relam = am.oid   JOIN pg_namespace AS NS ON i.relnamespace = NS.OID   JOIN pg_user AS U ON i.relowner = U.usesysid WHERE NOT nspname LIKE 'pg%'; -- Excluding system tables