Create table

Create a partitioned on a single dimension with enabled, or create a standard relational table. A is a specialized table that automatically partitions your data by time. All actions that work on a table, work on s. For example, ALTER TABLE and SELECT. to foreign keys are not allowed, all other combinations are permitted. As the data cools and becomes more suited for analytics, add a columnstore policy so your data is automatically converted to the after a specific time interval. This columnar format enables fast scanning and aggregation, optimizing performance for analytical workloads while also saving significant storage space. In the conversion, chunks are compressed by more than 90%, and organized for efficient, large-scale queries. This columnar format enables fast scanning and aggregation, optimizing performance for analytical workloads. You can also manually convert chunks in a to the . By default, a is partitioned on the time dimension. To add secondary dimensions to a , call add_dimension. To convert an existing relational table into a , call create_hypertable. CREATE TABLE extends the standard CREATE TABLE. This page explains the features and arguments specific to .

Samples

Create a hypertable partitioned on the time dimension and enable :

Create the hypertable:

CREATE TABLE crypto_ticks (
   "time" TIMESTAMPTZ,
   symbol TEXT,
   price DOUBLE PRECISION,
   day_volume NUMERIC
) WITH (
  tsdb.hypertable,
  tsdb.partition_column='time',
  tsdb.segmentby='symbol', 
  tsdb.orderby='time DESC'
);

Enable by adding a columnstore policy:

CALL add_columnstore_policy('crypto_ticks', after => INTERVAL '1d');

Create a hypertable partitioned on the time with fewer chunks based on time interval:

CREATE TABLE IF NOT EXISTS hypertable_control_chunk_interval(
 time int4 NOT NULL, 
 device text, 
 value float
) WITH (
 tsdb.hypertable,
 tsdb.partition_column='time',
 tsdb.chunk_interval=3453
);

Create a relational table

CREATE TABLE IF NOT EXISTS relational_table(
 device text, 
 value float
);

Arguments

The syntax is:

CREATE TABLE <table_name> (
   -- Standard Postgres syntax for CREATE TABLE  
) 
WITH (
   tsdb.hypertable = true | false
   tsdb.partition_column = '<column_name> ',
   tsdb.chunk_interval = '<interval>'
   tsdb.create_default_indexes =  true | false
   tsdb.associated_schema = '<schema_name>',
   tsdb.associated_table_prefix = '<prefix>'
   tsdb.orderby = '<column_name> [ASC | DESC] [ NULLS { FIRST | LAST } ] [, ...]',
   tsdb.segmentby = '<column_name> [, ...]',
)

Name	Type	Default	Required	Description
`tsdb.hypertable`	BOOLEAN	`true`	✖	Create a new hypertable for time-series data rather than a standard relational table.
`tsdb.partition_column`	TEXT	`true`	✖	Set the time column to automatically partition your time-series data by.
`tsdb.chunk_interval`	TEXT	`7 days`	✖	Change this to better suit your needs. For example, if you set `chunk_interval` to 1 day, each chunk stores data from the same day. Data from different days is stored in different chunks.
`tsdb.create_default_indexes`	BOOLEAN	`true`	✖	Set to `false` to not automatically create indexes. The default indexes are: On all hypertables, a descending index on `partition_column` On hypertables with space partitions, an index on the space parameter and `partition_column`
`tsdb.associated_schema`	REGCLASS	`_timescaledb_internal`	✖	Set the schema name for internal hypertable tables.
`tsdb.associated_table_prefix`	TEXT	`_hyper`	✖	Set the prefix for the names of internal hypertable chunks.
`tsdb.orderby`	TEXT	Descending order on the time column in `table_name`.	✖	The order in which items are used in the . Specified in the same way as an `ORDER BY` clause in a `SELECT` query.
`tsdb.segmentby`	TEXT	No segmentation by column.	✖	Set the list of columns used to segment data in the for `table`. An identifier representing the source of the data such as `device_id` or `tags_id` is usually a good candidate.

Returns

returns a simple message indicating success or failure.

Hypertables

Hypercore

Samples

Arguments

Returns

​Samples

​Arguments

​Returns

Samples

Arguments

Returns