skip to Main Content

I built this SQL:

SELECT 
    indexname 
FROM pg_indexes
WHERE schemaname = 'foo' AND 
      tablename = 'foo'

It returns me all indexes of a table. However, I would like the index names and the type of the indexes to be displayed, e.g. UNIQUE or PRIMARY.

2

Answers


  1. You can change your query to below. You can identify the key is unique or not from index definition (indexdef). Note that primary keys are also unique.

        SELECT 
            case when upper(indexdef) like '%UNIQUE%' then 'UNIQUE' else 'NOT_UNIQUE' end as case
        FROM pg_indexes 
        WHERE schemaname = 'foo' AND 
             tablename = 'foo'
    
    Login or Signup to reply.
  2. You can get this information from pg_index (not pg_indexes), like this:

    SELECT it.relname, i.indisunique, i.indisprimary 
    FROM pg_index i
    INNER JOIN pg_class ct on i.indrelid = ct.oid
    INNER JOIN pg_class it on i.indexrelid = it.oid
    WHERE ct.relname = 'foo';
    

    By way of explanation the indrelid column of pg_index contains the oid (in pg_class) of the table to which it belongs, whilst the indexrelid is the oid of the index itself.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search