show_chunks()

Get list of chunks associated with a hypertable.

Function accepts the following required and optional arguments. These arguments have the same semantics as the drop_chunks function.

Required Arguments

NameDescription
relation(REGCLASS) Hypertable or continuous aggregate from which to select chunks.

Optional Arguments

NameDescription
older_than(ANY) Specification of cut-off point where any full chunks older than this timestamp should be shown.
newer_than(ANY) Specification of cut-off point where any full chunks newer than this timestamp should be shown.

The older_than and newer_than parameters can be specified in two ways:

  • interval type: The cut-off point is computed as now() - older_than and similarly now() - newer_than. An error will be returned if an INTERVAL is supplied and the time column is not one of a TIMESTAMP, TIMESTAMPTZ, or DATE.

  • timestamp, date, or integer type: The cut-off point is explicitly given as a TIMESTAMP / TIMESTAMPTZ / DATE or as a SMALLINT / INT / BIGINT. The choice of timestamp or integer must follow the type of the hypertable’s time column.

When both arguments are used, the function returns the intersection of the resulting two ranges. For example, specifying newer_than => 4 months and older_than => 3 months will show all full chunks that are between 3 and 4 months old. Similarly, specifying newer_than => '2017-01-01' and older_than => '2017-02-01' will show all full chunks between ‘2017-01-01’ and ‘2017-02-01’. Specifying parameters that do not result in an overlapping intersection between two ranges will result in an error.

Sample Usage

Get list of all chunks associated with a table:

  1. SELECT show_chunks('conditions');

Get all chunks from hypertable conditions older than 3 months:

  1. SELECT show_chunks('conditions', older_than => INTERVAL '3 months');

Get all chunks from hypertable conditions before 2017:

  1. SELECT show_chunks('conditions', older_than => DATE '2017-01-01');