Copy SQL

WITH ind AS (SELECT 
n.nspname AS table_schema,
i.relname AS table_name,
i.oid AS ind_table_oid,
ix.indkey AS index_col,
EXISTS (SELECT *
FROM pg_constraint
WHERE ix.indexrelid=pg_constraint.conindid) AS is_supporting_constraint,
pg_get_indexdef(ix.indexrelid) as index_def
FROM pg_index ix INNER JOIN pg_class c ON ix.indexrelid=c.oid
INNER JOIN pg_class i ON ix.indrelid=i.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (n.nspname='public' OR a.rolname<>'postgres')),
ind_unnest AS (SELECT table_schema, table_name, is_supporting_constraint, index_def, ind_table_oid, ind_col_num, ordin
FROM ind, Unnest(ind.index_col) WITH ORDINALITY AS k(ind_col_num, ordin)),
ind_with_names as (SELECT table_schema, table_name, is_supporting_constraint,  a_index.attname AS index_col, ordin AS position_in_index, index_def
FROM ind_unnest i INNER JOIN pg_attribute a_index on i.ind_col_num = a_index.attnum AND i.ind_table_oid = a_index.attrelid AND a_index.attisdropped = false)
SELECT table_schema, table_name, index_col, string_agg(index_def || ' (' || (CASE WHEN is_supporting_constraint=TRUE THEN 'Constraint index' ELSE 'secondary index' END) || ')', ';
' ORDER BY position_in_index) AS indexes, Count(*) AS number_of_indexes
FROM ind_with_names
GROUP BY table_schema, table_name, index_col
HAVING Count(*)>1
ORDER BY Count(*) DESC, table_schema, table_name, index_col;