with excl as (select
o.conname,
(select nspname from pg_namespace where oid=c.relnamespace) as excl_schema,
c.relname as excl_table,
c.oid as excl_table_oid,
ix.indkey AS excl_col,
o.conexclop AS excl_op,
pg_get_indexdef(o.conindid) as index_def
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_index ix on ix.indexrelid=o.conindid
where o.contype in ('x') ),
excl_unnest as (select conname, excl_schema, excl_table, excl_table_oid, excl_col, excl_col_num, ordin_col, index_def, excl_op
from excl, unnest(excl.excl_col) with ordinality as k(excl_col_num, ordin_col)),
excl_with_names as (select conname, excl_schema, excl_table, array_agg(a_key.attname order by ordin_col) as excl_col, index_def, excl_op
from excl_unnest k left join pg_attribute a_key on k.excl_col_num = a_key.attnum and k.excl_table_oid = a_key.attrelid and a_key.attisdropped = false
group by conname, excl_schema, excl_table, index_def, excl_op),
excl_unnest_op as (select conname, excl_schema, excl_table, excl_col, index_def, excl_op, excl_op_num, ordin_op
from excl_with_names, unnest(excl_with_names.excl_op) with ordinality as k(excl_op_num, ordin_op)),
excl_with_names_op as (select conname, excl_schema, excl_table, excl_col, index_def, array_agg(op.oprname order by ordin_op) as excl_op, count(distinct op.oprname) as nr_of_distinct_operators
from excl_unnest_op k inner join pg_operator op on k.excl_op_num=op.oid
group by conname, excl_schema, excl_table, excl_col, index_def)
select conname, excl_schema, excl_table, excl_col, excl_op, index_def
from excl_with_names_op
where nr_of_distinct_operators=1
and excl_op[1] = '='
and index_def not like '% WHERE %'
and index_def not like '%(upper(%'
and index_def not like '%(lower(%'
order by excl_schema, excl_table, excl_col;