postgres materialized view - ghdrako/doc_snipets GitHub Wiki

• https://www.postgresql.org/docs/current/sql-creatematerializedview.html#:~:text=parameters

create materialized view transcripts as
    select
        students.name,
        students.type,
        courses.title,
        courses.code,
        grades.result
    from grades
    left join students on grades.student_id = students.id
    left join courses on grades.course_id = courses.id;

refresh materialized view transcripts; # Only the owner can then refresh mv 

CREATE MATERIALIZED VIEW command, specifying whether to populate it with data immediately or to leave it empty for manual data loading later.

CREATE MATERIALIZED VIEW view_name AS query WITH [NO] DATA;

CREATE MATERIALIZED VIEW order_details_mat AS
SELECT
o.order_id, o.order_date, c.first_name,
c.last_name, p.product_name, od.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'
WITH NO DATA;

This creates the materialized view without immediately loading data into it (WITH NO DATA). You can populate the view with data at any point using the REFRESH MATERIALIZED VIEW command. To refresh the materialized view and keep it up-to-date, run the following command:

REFRESH MATERIALIZED VIEW order_details_mat;

If you want the materialized view to be refreshed without locking it for reads, and assuming you’re using PostgreSQL version 9.4 or later, you can use the CONCURRENTLY option, so long as there is at least one unique index on the materialized view:

REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;

Materialized views are not automatically updated when the underlying data changes.

Indexes can be applied to materialized views to further enhance query performance, following the same principles as indexing tables. Additionally, PostgreSQL allows you to alter materialized views (for example, renaming columns or changing storage parameters) using the ALTER MATERIALIZED VIEW command, providing flexibility in managing the views post-creation. When a materialized view in PostgreSQL is refreshed, it empties the existing data and re-runs the SELECT query to repopulate the view. This process involves the following steps:

1. Clear the existing data: The current data in the materialized view is discarded.
2. Re-execute the SELECT query: The SELECT query defined for the materialized view is run again.
3. Populate with new data: The result of the SELECT query is used to repopulate the materialized view with fresh data.