refresh_continuous_aggregate() Community

Refresh all buckets of a continuous aggregate in the refresh window given by window_start and window_end.

A continuous aggregate materializes aggregates in time buckets (e.g., min, max, average over 1 day worth of data), as determined by the time_bucket interval specified when the continuous aggregate was created. Therefore, when refreshing the continuous aggregate, only buckets that completely fit within the refresh window will be refreshed. In other words, it is not possible to compute the aggregate over, for example, half a bucket. Therefore, any buckets that do no fit within the given refresh window will be excluded.

The function expects the window parameter values to have a time type that is compatible with the continuous aggregate’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 continuous aggregate 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 in order to align with bucket boundaries (for examples, see Sample Usage).

Required Arguments

NameDescription
continuous_aggregate(REGCLASS) The continuous aggregate to refresh.
window_startStart of the window to refresh (inclusive), has to be before window_end. NULL is equivalent to an open-ended start of the window.
window_endEnd of the window to refresh (exclusive), has to be after window_start. NULL is equivalent to open-ended end of the window.

Errors

An error is given if:

  • The refresh window does not cover at least one full bucket. Note that, even though the window is one bucket in size, it might not align with the start and the end of a bucket in the continuous aggregate.
  • The function is called in a transaction block.

Sample Usage

A continuous aggregate conditions_summary uses the TIMESTAMP WITH TIME ZONE type and has a bucket size of 1 day. To refresh only 1 day (bucket) between 2020-01-01 (inclusive) and 2020-01-02 (exclusive):

  1. CALL refresh_continuous_aggregate('conditions_summary', '2020-01-01 00:00 UTC', '2020-01-02 00:00 UTC');

If the local time zone is already UTC, the time zone information can be elided:

  1. CALL refresh_continuous_aggregate('conditions_summary', '2020-01-01', '2020-01-02');

Alternatively, refresh at least two buckets to ensure at least one bucket is refreshed, irrespective of local time zone:

  1. CALL refresh_continuous_aggregate('conditions_summary', '2020-01-01', '2020-01-03');

To refresh a longer interval, e.g., one month between 2020-01-01 (inclusive) and 2020-02-01 (exclusive):

  1. CALL refresh_continuous_aggregate('conditions_summary', '2020-01-01 00:00 UTC', '2020-02-01 00:00 UTC');