How to determine if postgres database contains GiST indexes and what type?

Question

Is there a way to easily check if a PostgreSQL database has any GiST indexes and of what type they are?

Answer 1

Whenever you need to examine the structure of your database via code, always think "I should look at information_schema or pg_catalog". information_schema contains a standardized schema (66 views), whereas pg_catalog is PostgreSQL-specific, but contains more info (97 tables or views).

select 
  * 
from
  pg_catalog.pg_indexes 
where 
  indexdef ~* '\ygist\y'

Will show you all the gist indexes and their tables, names, and definitions.

~* means match a regular expression, case-insensitive.

\y means word boundary, so it would find ' gist ' but not 'logistics' in that column