Tag Info

New answers tagged

3

Turns out this wasn't as complicated as I thought! (With just a little knowledge of pg_catalog...) Part 1: Query whether a materialized view exists: SELECT count(*) FROM pg_catalog.pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'm' AND n.nspname = 'some_schema' AND c.relname = 'some_mat_view'; Part 2: Query the definition of a ...


2

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_keys AS ( SELECT c.oid, c.relname, c2.relname AS idx_name FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, ...


2

As of PostgreSQL 9.4: Different to the documentation of CREATE VIEW, the documentation of CREATE MATERIALIZED VIEW does NOT mention the REPLACE keyword. There seems to be no shortcut aside from dropping all dependent objects and rebuilding each one. When you do so, I can only recommend two small things: Use DROP MATERIALIZED VIEW blabla CASCADE to get a ...



Top 50 recent answers are included