Get constraints and indexes on materialized views

Question

Query to get all indexes and all constraints on all materialized views in Postgres. The below query only returns indexes on tables.

SELECT indexname, indexdef 
FROM pg_indexes 
WHERE schemaname = 'public' AND tablename = 'table'

Answer 1

The view pg_indexes provides access to useful information about each index in the database (also indexes on materialized views). You can lookup pg_class to filter out only materialized views (relkind = 'm'):

select i.*
from pg_indexes i
join pg_class c
    on schemaname = relnamespace::regnamespace::text 
    and tablename = relname
where relkind = 'm'

 schemaname | tablename |    indexname    | tablespace |                             indexdef                             
------------+-----------+-----------------+------------+------------------------------------------------------------------
 public     | my_view   | my_view_id_idx  |            | CREATE UNIQUE INDEX my_view_id_idx ON my_view USING btree (id)
 public     | sen_view  | sen_view_id_idx |            | CREATE UNIQUE INDEX sen_view_id_idx ON sen_view USING btree (id)
(2 rows)