List MATERIALIZED VIEWs in PostgreSQL that do not have a UNIQUE index

Question

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.

• Sean the Bean: Somewhat-related question about querying materialized view definitions: dba.stackexchange.com/questions/102620

Answer 1

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;

• alfonx: Very fast and very helpfull. Thanks for sharing!
• alfonx: matviews_with_keys is listing all mat. views that have any index. For CONCURRENTLY to work, it must be UNIQUE indexes. (I will see if i get this improved...)
• alfonx: I eddited your code, adding a "and indisunique" to the WHERE. Hope thats OK. Now only UNIQUE indexes that can be used for REFRESH CONCURRENTLY wil be counted.