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.

NameTypeDescription
hypertable_schemaTEXTSchema name of the hypertable
hypertable_nameTEXTTable name of the hypertable
dimension_numberBIGINTDimension number of the hypertable, starting from 1
column_nameTEXTName of the column used to create this dimension
column_typeREGTYPEType of the column used to create this dimension
dimension_typeTEXTIs this a time based or space based dimension
time_intervalINTERVALTime interval for primary dimension if the column type is a time datatype
integer_intervalBIGINTInteger interval for primary dimension if the column type is an integer datatype
integer_now_funcTEXT`integer_now`` function for primary dimension if the column type is an integer datatype
num_partitionsSMALLINTNumber of partitions for the dimension
Note

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

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
Note

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);
SELECT table_name from create_hypertable('hyper_2dim', by_range('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 |

Keywords

Found an issue on this page?Report an issue or Edit this page in GitHub.