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
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.You can get this information from pg_index (not pg_indexes), like this:
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.