Is there a postgres command to list/drop all mater

Question

I am creating multiple views in my code and each time the code is run, I would like to drop all the materialized views generated thus far. Is there any command that will list all the materialized views for Postgres or drop all of them?

Answer 1

Show all:

SELECT oid::regclass::text
FROM   pg_class
WHERE  relkind = 'm';

Names are automatically double-quoted and schema-qualified where needed according to your current search_path in the cast from regclass to text.

In the system catalog pg_class materialized views are tagged with relkind = 'm'.
The manual:

m = materialized view

To drop all, you can generate the needed SQL script with this query:

SELECT 'DROP MATERIALIZED VIEW ' || string_agg(oid::regclass::text, ', ') 
FROM   pg_class
WHERE  relkind = 'm';

Returns:

DROP MATERIALIZED VIEW mv1, some_schema_not_in_search_path.mv2, ...

One DROP MATERIALIZED VIEW statement can take care of multiple materialized views. You may need to add CASCADE at the end if you have nested views.

Inspect the resulting DDL script to be sure before executing it. Are you sure you want to drop all MVs from all schemas in the db? And do you have the required privileges to do so? (Currently there are no materialized views in a fresh standard installation.)

Answer 2

This an answer is based on the answer from Erwin Brandstetter. The version below adds a specific schema name to only retrieve the materialized views from a defined schema. The Cascasde also drops dependencies on the materialized views from that schema. Be careful with that.

SELECT 'DROP MATERIALIZED VIEW <<schema_name>>.' || c.relname::text || ' CASCADE;' AS drop_statements
FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
AND c.relkind = 'm'
AND n.nspname = '<<schema_name>>'

Answer 3

This would be easier if you want to get a full list with the DROP statement in front of each view:

SELECT 'DROP MATERIALIZED VIEW ' || relname || ';' 
FROM   pg_class
WHERE  relkind = 'm';