Get metadata about the dimensions of hypertables, returning one row of metadata for each dimension of a hypertable. For a time-and-space-partitioned hypertable, for example, two rows of metadata are returned for the hypertable.
A time-based dimension column has either an integer datatype
(bigint, integer, smallint) or a time-related datatype
(timestamptz, timestamp, date).
The time_interval
column is defined for hypertables that use time datatypes.
Alternatively, for hypertables that use integer datatypes,
the integer_interval
and integer_now_func
columns are defined.
For space-based dimensions, metadata is returned that specifies their number
of num_partitions
. The time_interval
and integer_interval
columns are
not applicable for space based dimensions.
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 time based or space based dimension? |
time_interval | INTERVAL | Time interval for primary dimension if the column type is based on Postgres time datatypes |
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 integer based datatype |
num_partitions | SMALLINT | Number of partitions for the dimension |
Get information about the dimensions of hypertables.
--Create a time and space partitioned hypertableCREATE TABLE dist_table(time timestamptz, device int, temp float);SELECT create_hypertable('dist_table', 'time', 'device', chunk_time_interval=> INTERVAL '7 days', number_partitions=>3);SELECT * from timescaledb_information.dimensionsORDER BY hypertable_name, dimension_number;-[ RECORD 1 ]-----+-------------------------hypertable_schema | publichypertable_name | dist_tabledimension_number | 1column_name | timecolumn_type | timestamp with time zonedimension_type | Timetime_interval | 7 daysinteger_interval |integer_now_func |num_partitions |-[ RECORD 2 ]-----+-------------------------hypertable_schema | publichypertable_name | dist_tabledimension_number | 2column_name | devicecolumn_type | integerdimension_type | Spacetime_interval |integer_interval |integer_now_func |num_partitions | 2
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', 'a_col');SELECT add_dimension('hyper_2dim', 'b_col', chunk_time_interval=> '7 days');SELECT * FROM timescaledb_information.dimensions WHERE hypertable_name = 'hyper_2dim';-[ RECORD 1 ]-----+----------------------------hypertable_schema | publichypertable_name | hyper_2dimdimension_number | 1column_name | a_colcolumn_type | datedimension_type | Timetime_interval | 7 daysinteger_interval |integer_now_func |num_partitions |-[ RECORD 2 ]-----+----------------------------hypertable_schema | publichypertable_name | hyper_2dimdimension_number | 2column_name | b_colcolumn_type | timestamp without time zonedimension_type | Timetime_interval | 7 daysinteger_interval |integer_now_func |num_partitions |
Keywords
Found an issue on this page?
Report an issue!