Postgresql 9.4 allows to REFRESH materialized views CONCURRENTLY, if they have a UNIQUE index.
I am looking fo a SQL-query that lists all materialized views that DO NOT have such a UNIQUE or PRIMARY KEY index.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Tip: use psql's -E flag to get it to show you how what queries it is issuing for e.g. its
\dm meta-command, and adjust from there.
Here's a rough and not at all cleaned-up query, but it should do the trick...
WITH matviews_with_unqiue_keys AS ( SELECT c.oid, c.relname, c2.relname AS idx_name FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i LEFT JOIN pg_catalog.pg_constraint con ON ( conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u')) WHERE c.relkind = 'm' AND c.oid = i.indrelid AND i.indexrelid = c2.oid AND indisunique ) SELECT c.relname FROM pg_class c WHERE c.relkind = 'm' EXCEPT SELECT mwk.relname FROM matviews_with_unique_keys as mwk;