PostgreSQL - how to list all UNIQUE indexes for specific database?
You need to use pg_index and limit to those that have indisunique. To get the actual index and table names, you need to join that table to pg_class and pg_namespace:

select idx.relname as index_name, 
       insp.nspname as index_schema,
       tbl.relname as table_name,
       tnsp.nspname as table_schema
from pg_index pgi
  join pg_class idx on idx.oid = pgi.indexrelid
  join pg_namespace insp on insp.oid = idx.relnamespace
  join pg_class tbl on tbl.oid = pgi.indrelid
  join pg_namespace tnsp on tnsp.oid = tbl.relnamespace
where pgi.indisunique --&lt;&lt; only unique indexes
  and tnsp.nspname = 'public' --&lt;&lt; only for tables from the public schema