Skip to main content
Refresh all buckets of a in the refresh window given by window_start and window_end. A materializes aggregates in time buckets. For example, min, max, average over 1 day worth of data, and is determined by the time_bucket interval. Therefore, when refreshing the , only buckets that completely fit within the refresh window are refreshed. In other words, it is not possible to compute the aggregate over, for an incomplete bucket. Therefore, any buckets that do not fit within the given refresh window are excluded. The function expects the window parameter values to have a time type that is compatible with the ‘s time bucket expression—for example, if the time bucket is specified in TIMESTAMP WITH TIME ZONE, then the start and end time should be a date or timestamp type. Note that a using the TIMESTAMP WITH TIME ZONE type aligns with the UTC time zone, so, if window_start and window_end is specified in the local time zone, any time zone shift relative UTC needs to be accounted for when refreshing to align with bucket boundaries. To improve performance for refresh, see CREATE MATERIALIZED VIEW.

​
Samples

Refresh the conditions between 2020-01-01 and 2020-02-01 exclusive. 
CALL refresh_continuous_aggregate('conditions', '2020-01-01', '2020-02-01');
Alternatively, incrementally refresh the conditions between 2020-01-01 and 2020-02-01 exclusive, working in 12h intervals: 
DO
$$
DECLARE
  refresh_interval INTERVAL = '12h'::INTERVAL;
  start_timestamp TIMESTAMPTZ = '2020-01-01T00:00:00Z';
  end_timestamp TIMESTAMPTZ = start_timestamp + refresh_interval;
BEGIN
  WHILE start_timestamp < '2020-02-01T00:00:00Z' LOOP
    CALL refresh_continuous_aggregate('conditions', start_timestamp, end_timestamp);
    COMMIT;
    RAISE NOTICE 'finished with timestamp %', end_timestamp;
    start_timestamp = end_timestamp;
    end_timestamp = end_timestamp + refresh_interval;
  END LOOP;
END
$$;
Force the conditions to refresh between 2020-01-01 and 2020-02-01 exclusive, even if the data has already been refreshed. 
CALL refresh_continuous_aggregate('conditions', '2020-01-01', '2020-02-01', force => TRUE);

​
Arguments

The syntax is: 
CALL refresh_continuous_aggregate(
    continuous_aggregate = '<view_name>',
    window_start = <interval>,
    window_end = <interval>,
    force = true | false,
    options = '<jsonb_options>'
);
NameTypeDefaultRequiredDescription
continuous_aggregateREGCLASS-✔The to refresh.
window_startINTERVAL, TIMESTAMPTZ, INTEGER-✔Start of the window to refresh, has to be before window_end.
window_endINTERVAL, TIMESTAMPTZ, INTEGER-✔End of the window to refresh, has to be after window_start.
forceBOOLEANFALSE-Force refresh every bucket in the time range between window_start and window_end, even when the bucket has already been refreshed. This can be very expensive when a lot of data is refreshed.
refresh_newest_firstBOOLEANTRUE-Set to FALSE to refresh the oldest data first.
You must specify the window_start and window_end parameters differently, depending on the type of the time column of the . For s with TIMESTAMP, TIMESTAMPTZ, and DATE time columns, set the refresh window as an INTERVAL type. For s with integer-based timestamps, set the refresh window as an INTEGER type.
A NULL value for window_start is equivalent to the lowest changed element in the raw hypertable of the CAgg. A NULL value for window_end is equivalent to the largest changed element in raw hypertable of the CAgg. As changed element tracking is performed after the initial CAgg refresh, running CAgg refresh without window_start and window_end covers the entire time range.
Note that it’s not guaranteed that all buckets will be updated: refreshes will not take place when buckets are materialized with no data changes or with changes that only occurred in the secondary table used in the JOIN.