5

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.

1 Answer 1

10

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
2
  • can I limit the query only for specific database (that is only for the tables which belong to specific database)? Thanks
    – Dejan
    Jun 24, 2019 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
    – user1822
    Jun 25, 2019 at 5:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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