Is there a postgres command to list/drop all materialized views?
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;
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>>'