8

How do I check if a materialized view exists?

I have created one and checked in information_schema.tables and information_schema.viewsbut I cannot see it.

Where should I be looking?

klin
  • 112,967
  • 15
  • 204
  • 232
Guerrilla
  • 13,375
  • 31
  • 109
  • 210

1 Answers1

9

Use the system catalog pg_class, e.g.:

create materialized view my_view as select 1;

select relname, relkind
from pg_class
where relname = 'my_view'
and relkind = 'm';

 relname | relkind 
---------+---------
 my_view | m
(1 row)

or the system view pg_matviews:

select *
from pg_matviews
where matviewname = 'my_view';  

 schemaname | matviewname | matviewowner | tablespace | hasindexes | ispopulated | definition 
------------+-------------+--------------+------------+------------+-------------+------------
 public     | my_view     | postgres     |            | f          | t           |  SELECT 1;
(1 row)
klin
  • 112,967
  • 15
  • 204
  • 232
  • 1
    To actually check that materialized view exists use `select count(*)` instead of simple `select *`. In case of 1 - it exists, 0 - you get the idea.. – chill appreciator Sep 21 '21 at 12:14
  • 1
    @standalone - not exactly. There may be more than one view with the same name, so rather `select count (*) > 0` (returns Boolean). – klin Sep 21 '21 at 12:26