/* Show all key/indexed columns and their referenced table/column (if any) */ 

SELECT DISTINCT kcu.table_name, kcu.column_name, 
       CASE WHEN tc.constraint_type='PRIMARY KEY' THEN '0PRIMARY KEY' ELSE tc.constraint_type END AS constraint_type,  
       kcu.constraint_name, kcu.ordinal_position,
       CASE WHEN tc.constraint_type='FOREIGN KEY' THEN ccu.table_name  END AS ref_table_name,
       CASE WHEN tc.constraint_type='FOREIGN KEY' THEN ccu.column_name END AS ref_column_name
FROM information_schema.key_column_usage        kcu 
JOIN information_schema.table_constraints       tc   USING(constraint_name)
JOIN information_schema.constraint_column_usage ccu  USING(constraint_name) 
UNION  
SELECT tablename, SUBSTRING(indexdef, '\(.+\)'), 'INDEX', indexname, null, null, null
FROM   pg_indexes
WHERE  schemaname='public'
  AND  indexdef not like '%UNIQUE%'
 ORDER BY 1,3,4,2,5;