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.
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.
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'
