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

NameTypeDescription
continuous_aggregateREGCLASSThe continuous aggregate to add the policy for
start_offsetINTERVAL or integerStart 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_offsetINTERVAL or integerEnd 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_intervalINTERVALInterval 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

NameTypeDescription
if_not_existsBOOLEANSet to true to issue a notice instead of an error if the job does not exist. Defaults to false.

Returns

ColumnTypeDescription
job_idINTEGERTimescaleDB 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:

SELECT add_continuous_aggregate_policy('conditions_summary',
start_offset => INTERVAL '1 month',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour');

Found an issue on this page?

Report an issue!

Keywords

Related Content