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
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
SELECT, etc., still work
on the resulting hypertable.
For more information about using hypertables, including chunk size partitioning, see the hypertable section.
This reference describes the new generalized hypertable API that was introduced in TimescaleDB 2.13. The old interface for
create_hypertable is also available.
|Identifier of table to convert to hypertable.
|Dimension builder for the column to partition on.
|Whether to create default indexes on time/partitioning columns. Default is TRUE.
|Whether to print warning if table already converted to hypertable or raise exception. Default is FALSE.
|Set to TRUE to migrate any existing data from the
relation table to chunks in the new hypertable. A non-empty table generates an error without this option. Large tables may take significant time to migrate. Defaults to FALSE.
|ID of the hypertable in TimescaleDB.
|TRUE if the hypertable was created, FALSE when
if_not_exists is true and no hypertable was created.
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
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, but the reverse is not allowed. 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
SHARE ROW EXCLUSIVE lock on the referenced tables before calling
create_hypertable in the same transaction, see
for the syntax.
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.
conditions to hypertable with just range partitioning on column
conditions to hypertable, setting
chunk_time_interval to 24 hours.
conditions to hypertable. Do not raise a warning
conditions is already a hypertable:
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:
Time partition table
events, on a column type
event), which has
a top level key (
started) containing an ISO 8601 formatted timestamp:
Found an issue on this page?Report an issue!