create_hypertable()

Creates a TimescaleDB hypertable from a PostgreSQL table (replacing the latter), partitioned on one dimension. The PostgreSQL table cannot be an already partitioned table (declarative partitioning or inheritance). In case of a non-empty table, it is possible to migrate the data during hypertable creation using the migrate_data option, although this might take a long time and has certain limitations when the table contains foreign key constraints (see below).

After creation, all actions, such as ALTER TABLE, SELECT, etc., still work on the resulting hypertable.

For more information about using hypertables, including chunk size partitioning, see the hypertable section.

Note

This reference describes the new generalized hypertable API that was introduced in TimescaleDB 2.13. The old interface for create_hypertable is also available.

Note

If you use SELECT * FROM create_hypertable(...) you get the return value formatted as a table with column headings.

The use of the migrate_data argument to convert a non-empty table can lock the table for a significant amount of time, depending on how much data is in the table. It can also run into deadlock if foreign key constraints exist to other tables.

When converting a normal SQL table to a hypertable, pay attention to how you handle constraints. A hypertable can contain foreign keys to normal SQL table columns and vice versa. However, this does not work between hypertables. UNIQUE and PRIMARY constraints must include the partitioning key.

The deadlock is likely to happen when concurrent transactions simultaneously try to insert data into tables that are referenced in the foreign key constraints and into the converting table itself. The deadlock can be prevented by manually obtaining SHARE ROW EXCLUSIVE lock on the referenced tables before calling create_hypertable in the same transaction, see PostgreSQL documentation for the syntax.

Note

The time column in create_hypertable must be defined as NOT NULL. If this is not already specified on table creation, create_hypertable automatically adds this constraint on the table when it is executed.

When creating a hypertable, you need to provide dimension info using one of the dimension builders). This is used to specify what column to partition by and in what way to partition.

Convert table conditions to hypertable with just range partitioning on column time:

SELECT create_hypertable('conditions', by_range('time'));

Copy

Convert table conditions to hypertable, setting chunk_time_interval to 24 hours.

SELECT create_hypertable('conditions', by_range('time', 86400000000));
SELECT create_hypertable('conditions', by_range('time', INTERVAL '1 day'));

Copy

Convert table conditions to hypertable. Do not raise a warning if conditions is already a hypertable:

SELECT create_hypertable('conditions', by_range('time'), if_not_exists => TRUE);

Copy

Time partition table measurements on a composite column type report using a range partitioning function. Requires an immutable function that can convert the column value into a supported column value:

CREATE TYPE report AS (reported timestamp with time zone, contents jsonb);

CREATE FUNCTION report_reported(report)
  RETURNS timestamptz
  LANGUAGE SQL
  IMMUTABLE AS
  'SELECT $1.reported';

SELECT create_hypertable('measurements', by_range('report', partition_func => 'report_reported'));

Copy

Time partition table events, on a column type jsonb (event), which has a top level key (started) containing an ISO 8601 formatted timestamp:

CREATE FUNCTION event_started(jsonb)
  RETURNS timestamptz
  LANGUAGE SQL
  IMMUTABLE AS
  $func$SELECT ($1->>'started')::timestamptz$func$;

SELECT create_hypertable('events', by_range('event', partition_func => 'event_started'));

Copy