5

Given for example a materialized view like this (Postgres 10.3):

create materialized view my_view as
    select * from my_table where sell_date < '2018-03-01';

The sell_date comparison value ('2018-03-01') can change sometime, but I want to avoid to drop and recreate the materialized view each time. The only idea I came up with it is using an external table with some metadata values like the date needed:

create materialized view my_view as
    select * from my_table where sell_date < 
        (select original_sell_date from some_metadata_table);

Is there any other way to work around this limitation of materialized views on Postgres?

One of the problems with my current solution is that you can have two or more materialized views that use the same value, but they may need at some point of different values. In this case a duplication of the metadata table is needed.

  • Why are you using a materialized view for such a basic query? – Colin 't Hart Jun 3 '18 at 8:37
  • @Colin'tHart it is just an example – Randomize Jun 3 '18 at 9:17
1

There are three ways,

  • Use foreign data in a table that holds the inputs.
  • Make sell_date a function of the current time. Such as AND sell_date > current_time - '5 days'.
  • If nothing depends on it, drop and reload a new ad-hoc MATERIALIZED VIEW by the same name in a transaction.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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