Show indexes that are eligible for a `NOT NULL` constraint

gistfile1.txt

select
    t.relname as table_name,
    i.relname as index_name,
    a.attname as column_name,
    pg_size_pretty(pg_relation_size(i.relname::regclass)) as index_size,
    st.null_frac as null_frac
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a,
    pg_stats st
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and st.tablename = t.relname
    and st.attname = a.attname
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and st.null_frac > 0.5
    and st.schemaname = current_schema
order by
    t.relname,
    i.relname;