CODEWITHSUNDEEP
postgresqlmaterialized-views
user11094471
user11094471

Reputation:

How to refresh a materialized view as soon as the day gets over in postgres

I'm working on a project which requires me to write a query to create a materialized view in postgres. My requirement is that the materialized view must refresh itself periodically everyday only at 12am. Though I found some information that may be relevant on other places in the web, I wasn't so sure to modify and implement, as it may result to be too costly for me plus i'm not too experienced in sql.

Upvotes: 1

Views: 8777

Answers (1)

madflow
madflow

Reputation: 8490

As the documentation states:

https://www.postgresql.org/docs/current/sql-creatematerializedview.html

CREATE MATERIALIZED VIEW defines a materialized view of a query. The query is executed and used to populate the view at the time the command is issued (unless WITH NO DATA is used) and may be refreshed later using REFRESH MATERIALIZED VIEW.

So in order to refresh a Materialized View you will need:

https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html

REFRESH MATERIALIZED VIEW

In order to to this periodically you will need some kind of cron implementation.

A Postgresql "inhouse" solution could be: https://github.com/citusdata/pg_cron

Upvotes: 1

Related Questions