0

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.

3

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
  • can I limit the query only for specific database (that is only for the tables which belong to specific database)? Thanks – Dejan Jun 24 at 21:23
  • @Dejan: queries are always for the current database. You can't get that information from a different database than the current one – a_horse_with_no_name Jun 25 at 5:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.