How do I discover the underlying query of a materialized view I created?

Question

I created a materialized view in Postgres 9.3 but I have since lost the underlying SELECT query that created it. I would like to DROP the materialized view, rewrite the query to include more data, and then CREATE a materialized view of the same name but with a new underlying query.

Answer 1

Just:

SELECT pg_get_viewdef('myview');

from the client of your choice.

e.g. in psql:

test=> CREATE MATERIALIZED VIEW fred AS SELECT x FROM generate_series(1,100) x;
SELECT 100
test=> \a\t
Output format is unaligned.
Showing only tuples.
test=> SELECT pg_get_viewdef('fred');
 SELECT x.x
   FROM generate_series(1, 100) x(x);

This works for normal and materialized views.

Alternately, as Richard says, use psql's \d+, which calls pg_get_viewdef behind the scenes.

Answer 2

SELECT * FROM "pg_catalog"."pg_matviews"

That's how you find a list of all the materialized views you created. I'd never used or seen the pg_catalog schema before and Navicat, the GUI I use, was hiding "system items" that included pg_catalog. You can turn turn hiding off in the app's preferences.