Feedback on whether index was created on materialized views in postgresql-CodePudding

I created a unique index for a materialized view as :

create unique index if not exists matview_key on
      matview (some_group_id, some_description);

I can't tell if it has been created
How do I see the index?

Thank you!

CodePudding user response：

Two ways to verify index creation:

--In psql
\d matview

--Using SQL

select 
  * 
from 
   pg_indexes 
where 
   indexname = 'matview_key' 
and 
   tablename = 'matview';

More information on pg_indexes.

CodePudding user response：

Like has been commented, if the command finishes successfully and you don't get an error message, the index was created. Possible caveat: while the transaction is not committed, nobody else can see it (except the unique name is reserved now), and it still might get rolled back. Check in a separate transaction to be sure.

To be absolutely sure:

SELECT pg_get_indexdef(oid)
FROM   pg_catalog.pg_class
WHERE  relname = 'matview_key'
AND    relkind = 'i'
-- AND    relnamespace = 'public'::regnamespace  -- optional, to make sure of the schema, too

This way you see whether an index of the given name exists, and also its exact definition to rule out a different index with the same name. Pure SQL, works from any client. (There is nothing special about an index on materialized views.)

Also filter for the schema to be absolutely sure. Would be the "default" schema (a.k.a. "current" schema) in your case, since you did not specify in the creation. See:

How does the search_path influence identifier resolution and the "current schema"

In psql:

\di public.matview_key

To only find indexes. Again, the schema is optional to narrow down.

Progress Reporting

If creating an index takes a long time, you can look up progress in pg_stat_progress_create_index since Postgres 12:

SELECT * FROM pg_stat_progress_create_index
-- WHERE relid = 'public.matview'::regclass  -- optionally narrow down

CodePudding user response：

Un alternative to looking into pg_indexes is pg_matviews (for a materialized view only)

select *
from pg_matviews
where matviewname = 'my_matview_name';