Postgresql – Refresh a PostgreSQL materialized view automatically without using triggers

materialized-viewpostgresqlpostgresql-9.5

I created a materialized view named view_table_A on a foreign data wrapper table table_A. What I want is for the view to be automatically updated after every new insert in table_A. I tried to do this with triggers but it didn't work.

Is it possible to refresh a materialized view automatically without using triggers?

Best Answer

As a_horse_with_no_name said in a comment:

No, that's not possible. You need some kind of scheduler that runs refresh materialized view e.g. pg_cron or something on the operating system level – a_horse_with_no_name

Alternatively, if you need a MATERIALIZED VIEW that refreshes when you run SELECT, just remove MATERIALIZED and use a regular VIEW. Materialization only adds a periodic cache. It's only needed when the query itself is prohibitively slow or hot.

Related Question