Oracle

  SELECT distinct (i.index_name),
                   decode(i.uniqueness,'UNIQUE','UNIQUE','')
              FROM all_indexes i,
                   all_ind_partitions ip
             WHERE i.owner = ? AND
                   i.table_name = ? AND
                   ip.index_owner = ? AND
                   ip.index_name = i.index_name AND
             ORDER BY i.index_name;

i have written below query I know its not correct someone can tell me the equivalent postgress query

select disinct(pgc_index.relname),
           CASE pgi.indisunique 
                WHEN UNIQUE 
                    THEN 'UNIQUE'
                    ELSE ''
            END AS 'uniquness',
        from pg_index pgi
    join pg_class pgc_index on pgi.indexrelid = pgc_index.oid AND UPPER(pgc_index.relkind) = 'I'
    where substring(pg_get_expr(pgc_index .relpartbound, pgc_index .oid, true) =? AND
    pgi.indrelid = to_regclass(?) AND      
    pgi.indexrelid = pgc_index.oid AND
    pgc_index.relowner = (SELECT usesysid FROM pg_user WHERE usename=?)
    order by pgc_index.relname