32

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).
4

4 Answers 4

42

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

16

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.

0
2

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

-2

Postgres 14.5

SELECT schemaname, matviewname, definition FROM <database_name>.pg_catalog.pg_matviews;
5
  • 1
    How's that different from the answer posted six years earlier?
    – mustaccio
    Sep 28, 2022 at 16:39
  • It's a one-liner and includes the selection of the database and the schema where the view exists, which was missing in such answer. Sep 28, 2022 at 17:51
  • So, in your Postgres 14 cross-database references are implemented?
    – mustaccio
    Sep 28, 2022 at 18:23
  • Using Intellij database module, when I write a query it executes either, in the database I have selected, or in any database specified in the query. In this case, I used a query that doesn't require me to select a database in advance. Sep 29, 2022 at 10:30
  • Furthermore, the answer relevant to Postgres 9.3+, assumes that ` FROM pg_matviews ` works. However, I had to use pg_catalog.pg_matviews. I don't see any other answer in this thread covering that scenario. I'm not an expert in Postgres, so it might be that I'm missing something obvious, but none of the existing answers worked for me out of the box. Therefore my attempt to contribute. Not sure if the downvotes are due to duplication or because it's a bad answer; In which case I'd love to understand why and correct accordingly. Sep 29, 2022 at 10:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.