CREATE TABLE

Create a hypertable partitioned on a single dimension with columnstore enabled, or create a standard PostgreSQL relational table.

A hypertable is a specialized PostgreSQL table that automatically partitions your data by time. All actions that work on a PostgreSQL table, work on hypertables. For example, ALTER TABLE and SELECT.

hypertable to hypertable 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 columnstore 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 columnstore conversion, hypertable 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 hypertable to the columnstore.

By default, a hypertable is partitioned on the time dimension. To add secondary dimensions to a hypertable, call add_dimension.

To convert an existing relational table into a hypertable, call create_hypertable.

CREATE TABLE extends the standard PostgreSQL CREATE TABLE. This page explains the features and arguments specific to TimescaleDB.

• Create a hypertable partitioned on the time dimension and enable columnstore:

  1. 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'
     );

     Copy

  2. Enable hypercore by adding a columnstore policy:

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

     Copy

• 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
  );

  Copy

• Create a PostgreSQL relational table

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

  Copy

The syntax is:

CREATE TABLE <table_name> (
   -- Standard PostgreSQL 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> [, ...]',
)

Copy

TimescaleDB returns a simple message indicating success or failure.