timescaledb_information.dimensions

Since 0.1.0

Returns information about the dimensions of a hypertable. hypertables can be partitioned on a range of different dimensions. By default, all hypertables are partitioned on time, but it is also possible to partition on other dimensions in addition to time.

For hypertables that are partitioned solely on time, timescaledb_information.dimensions returns a single row of metadata. For hypertables that are partitioned on more than one dimension, the call returns a row for each dimension.

For time-based dimensions, the metadata returned indicates the integer datatype, such as BIGINT, INTEGER, or SMALLINT, and the time-related datatype, such as TIMESTAMPTZ, TIMESTAMP, or DATE. For space-based dimension, the metadata returned specifies the number of num_partitions.

If the hypertable uses time data types, the time_interval column is defined. Alternatively, if the hypertable uses integer data types, the integer_interval and integer_now_func columns are defined.

Samples

Get information about the dimensions of hypertables.

-- Create a range and hash partitioned hypertable
CREATE TABLE dist_table(time timestamptz, device int, temp float);
SELECT create_hypertable('dist_table', by_range('time', INTERVAL '7 days'));
SELECT add_dimension('dist_table', by_hash('device', 3));

SELECT * from timescaledb_information.dimensions
  ORDER BY hypertable_name, dimension_number;

-[ RECORD 1 ]-----+-------------------------
hypertable_schema | public
hypertable_name   | dist_table
dimension_number  | 1
column_name       | time
column_type       | timestamp with time zone
dimension_type    | Time
time_interval     | 7 days
integer_interval  |
integer_now_func  |
num_partitions    |
-[ RECORD 2 ]-----+-------------------------
hypertable_schema | public
hypertable_name   | dist_table
dimension_number  | 2
column_name       | device
column_type       | integer
dimension_type    | Space
time_interval     |
integer_interval  |
integer_now_func  |
num_partitions    | 2

The by_range and by_hash dimension builders are an addition to TimescaleDB 2.13.

Get information about dimensions of a hypertable that has two time-based dimensions.

CREATE TABLE hyper_2dim (a_col date, b_col timestamp, c_col integer) WITH (
  tsdb.hypertable,
  tsdb.partition_column = 'a_col'
);
SELECT add_dimension('hyper_2dim', by_range('b_col', INTERVAL '7 days'));

SELECT * FROM timescaledb_information.dimensions WHERE hypertable_name = 'hyper_2dim';

-[ RECORD 1 ]-----+----------------------------
hypertable_schema | public
hypertable_name   | hyper_2dim
dimension_number  | 1
column_name       | a_col
column_type       | date
dimension_type    | Time
time_interval     | 7 days
integer_interval  |
integer_now_func  |
num_partitions    |
-[ RECORD 2 ]-----+----------------------------
hypertable_schema | public
hypertable_name   | hyper_2dim
dimension_number  | 2
column_name       | b_col
column_type       | timestamp without time zone
dimension_type    | Time
time_interval     | 7 days
integer_interval  |
integer_now_func  |
num_partitions    |

Returns

Name	Type	Description
hypertable_schema	TEXT	Schema name of the hypertable
hypertable_name	TEXT	Table name of the hypertable
dimension_number	BIGINT	Dimension number of the hypertable, starting from 1
column_name	TEXT	Name of the column used to create this dimension
column_type	REGTYPE	Type of the column used to create this dimension
dimension_type	TEXT	Is this a time based or space based dimension
time_interval	INTERVAL	Time interval for primary dimension if the column type is a time datatype
integer_interval	BIGINT	Integer interval for primary dimension if the column type is an integer datatype
integer_now_func	TEXT	`integer_now“ function for primary dimension if the column type is an integer datatype
num\_partitions\	SMALLINT	Number of partitions for the dimension

The time_interval and integer_interval columns are not applicable for space based dimensions.