PostgreSQL Output which types/key an index has-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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'