Search code examples
sqlpostgresqlpsqlmaterialized-views

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

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.

Solution

  • 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.