Query the definition of a materialized view in Postgres

Question

I'm wondering how to query the definition of a materialized view in Postgres. For reference, what I hoped to do is very similar to what you can do for a regular view:

SELECT * FROM information_schema.views WHERE table_name = 'some_view';

which gives you the following columns:

table_catalog
table_schema
table_name
view_definition
check_option
is_updatable
is_insertable_into
is_trigger_updatable
is_trigger_deletable
is_trigger_insertable_into

Is this possible for materialized views?

From my research so far, it appears that materialized views are deliberately excluded from information_schema, because

The information_schema can only show objects that exist in the SQL standard.

(http://www.postgresql.org/message-id/[email protected])

Since they appear to being entirely excluded from information_schema, I'm not sure how to go about this, but what I'd like to do is twofold:

1. Query whether a particular materialized view exists. (So far the only way I've found to do this is try creating a mat view with the same name and see if it blows up.)
2. And then query the definition of the materialized view (similar to the view_definition column on information_schema.views).

Answer 1

Looks like 9.3 and up you can do:

select * from pg_matviews;
select * from pg_matviews where matviewname = 'view_name';

More info found here: https://stackoverflow.com/questions/29297296/postgres-see-query-used-to-create-materialized-view

Answer 2

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(*) > 0
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';

Nice and easy.

Part 2: Query the definition of a materialized view:

In order to come up with a query to get the definition of the mat view, I first had to look up the definition of the information_schema.views view by running:

SELECT view_definition
FROM information_schema.views
WHERE table_schema = 'information_schema'
AND table_name = 'views';

Then I copied out the query and changed c.relkind = 'v'::"char" to c.relkind = 'm'::"char" in order to get mat views (instead of regular views). See the full query here: http://pastebin.com/p60xwfes

At this point you could pretty easily add AND c.relname = 'some_mat_view' and run it to get the definition of some_mat_view.

But you'd still have to do this all over again next time you want to look up the definition of a mat view...

Bonus: Create a view to make this easier

I opted to create a new view to make it easier to look up mat view definitions in the future. I basically just added CREATE VIEW materialized_views AS to the beginning of the query linked above to create the new view, and now I can query it like so:

SELECT *
FROM materialized_views
WHERE table_schema = 'some_schema'
AND table_name = 'some_mat_view';

Much better!

I can also use this view to easily query whether a materialized view exists by changing * to count(*) > 0.

Disclaimer: I don't know it the other columns in the query results are entirely correct, since materialized views are fundamentally different from standard views (I think they're right). But this does at least query the table_schema, table_name and view_definition correctly.

Answer 3

The drawback with the other answers here is that you just get the SQL definition, while in most cases you are interested in the actual columns, and being able to manipulate them as text. The following is my answer from a similar question, which includes column names and datatypes:

I can't say I fully understand the underlying data model, so use my solution below with a grain of salt:

select 
    ns.nspname as schema_name, 
    cls.relname as table_name, 
    attr.attname as column_name,
    trim(leading '_' from tp.typname) as datatype
from pg_catalog.pg_attribute as attr
join pg_catalog.pg_class as cls on cls.oid = attr.attrelid
join pg_catalog.pg_namespace as ns on ns.oid = cls.relnamespace
join pg_catalog.pg_type as tp on tp.typelem = attr.atttypid
where 
    ns.nspname = 'your_schema' and
    cls.relname = 'your_materialized_view' and 
    not attr.attisdropped and 
    cast(tp.typanalyze as text) = 'array_typanalyze' and 
    attr.attnum > 0
order by 
    attr.attnum

You have to change 'your_schema'and 'your_materialized_view'.

Answer 4

Postgres 14.5

SELECT schemaname, matviewname, definition FROM <database_name>.pg_catalog.pg_matviews;