add_continuous_aggregate_policy()

Create a policy that automatically refreshes a continuous aggregate.

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 eqivalent 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 eqivalent 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.

warning

While setting end_offset to NULL is possible, it is not recommended. To include the most recent data in your aggregates, use real-time aggregation instead.

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!

Related Content