database - Is there a postgres command to list/drop all materialized views?

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

Pure SQL

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.)

Meta command in psql

In the default interactive terminal psql, you can use the meta-command:

dm

Executes this query on the server:

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('m','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

Which can be reduced to:

SELECT n.nspname as "Schema"
     , c.relname as "Name"
     , pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM   pg_catalog.pg_class c
LEFT   JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relkind = 'm'
AND    n.nspname <> 'pg_catalog'
AND    n.nspname <> 'information_schema'
AND    n.nspname !~ '^pg_toast'
AND    pg_catalog.pg_table_is_visible(c.oid)
ORDER  BY 1,2;