pg_matviews lists each PostgreSQL Materialized View and its reconstructed SELECT definition. A cross-platform customer requested this metadata.
SELECT schemaname,matviewname,matviewowner,tablespace,hasindexes,ispopulated,definition
FROM pg_catalog.pg_matviews ORDER BY schemaname,matviewname;The catalog includes schema, name, owner, tablespace, indexes, population state and definition. Filter by schema and view name for one object. NULL tablespace identifies the default.
ispopulated does not establish a nonempty or current result. hasindexes can describe present or recently existing indexes. Check the index catalogs for exact current details.
A materialized view persists a result that needs a refresh plan. Its definition is a reconstructed SELECT rather than complete original DDL. Preserve indexes, grants and operational settings separately. This catalog is not newly introduced whenever the current release changes.
Reference: PostgreSQL materialized-view catalog columns.
Related reading
- Comprehensive Database Performance Health Check
- SQL in Sixty Seconds series
- Generate Script of SQL Server Objects – SQL in Sixty Seconds #184
- Prevent Unauthorized Index Modifications – SQL in Sixty Seconds #183
- MAX Columns Ever Existed in Table – SQL in Sixty Seconds #182
- Tuning Query Cost 100% – SQL in Sixty Seconds #181
- Queries Using Specific Index – SQL in Sixty Seconds #180
- Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
- One Scan for 3 Count Sum – SQL in Sixty Seconds #178
- SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
- COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
A population flag is not a freshness guarantee, it is catalog state separate from row count and source changes.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
