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?
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.)
While using the standard 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;