PostgreSQL - how to list all UNIQUE indexes for specific database?

Question

I would like to get a list of all UNIQUE indexes (for unique column combinations) for specific database in PostgreSQL. I searched but could not find the query for this.

I found these two queries.

SELECT indexname FROM pg_indexes;
SELECT * from pg_indexes where schemaname = 'public'; 

but neither the one, suits my needs. I only need just those which identify indexes for unique columns. Thanks.

Answer 1

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 --<< only unique indexes
  and tnsp.nspname = 'public' --<< only for tables from the public schema

To exclude primary keys, you should filter out those that have indisprimary adding this condition in the where clause:

   ...
   and pgi.indisprimary = false