add_continuous_aggregate_policy()

Create a policy that automatically refreshes a continuous aggregate. To view the policies that you set or the policies that already exist, see informational views.

Required arguments

Name Type Description
continuous_aggregate REGCLASS The continuous aggregate to add the policy for
start_offset INTERVAL or integer Start of the refresh window as an interval relative to the time when the policy is executed. NULL is equivalent to MIN(timestamp) of the hypertable.
end_offset INTERVAL or integer End of the refresh window as an interval relative to the time when the policy is executed. NULL is equivalent to MAX(timestamp) of the hypertable.
schedule_interval INTERVAL Interval between refresh executions in wall-clock time. Defaults to 24 hours

The start_offset should be greater than end_offset.

You must specify the start_offset and end_offset parameters differently, depending on the type of the time column of the hypertable:

  • For hypertables with TIMESTAMP, TIMESTAMPTZ, and DATE time columns, set the offset as an INTERVAL type.
  • For hypertables with integer-based timestamps, set the offset as an INTEGER type.
important

While setting end_offset to NULL is possible, it is not recommended. By default, querying a continuous aggregate returns data between end_offset and the current time. There is no need to set end_offset to NULL. To learn more about how continuous aggregates use real-time aggregation, see the real-time aggregation section.

Optional arguments

Name Type Description
if_not_exists BOOLEAN Set to true to issue a notice instead of an error if the job does not exist. Defaults to false.

Returns

Column Type Description
job_id INTEGER TimescaleDB background job ID created to implement this policy

Sample use

Add a policy that refreshes the last month once an hour, excluding the latest hour from the aggregate. For performance reasons, we recommend that you exclude buckets that see lots of writes:

2. SELECT add_continuous_aggregate_policy('conditions_summary',

4. start_offset => INTERVAL '1 month',

6. end_offset => INTERVAL '1 hour',

8. schedule_interval => INTERVAL '1 hour');