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.

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;