--- title: create_hypertable() | Tiger Data Docs description: Create a hypertable --- Since [0.1.0](https://github.com/timescale/timescaledb/releases/tag/0.1.0) Replace a standard PostgreSQL relational table with a [hypertable](/docs/learn/hypertables/understand-hypertables/index.md) that is partitioned on a single dimension. To create a new hypertable, best practice is to call [CREATE TABLE](https://docs.tigerdata.com/api/latest/hypertable/create_table/). A hypertable is a PostgreSQL table that automatically partitions your data by time. A dimension defines the way your data is partitioned. All actions work on the resulting hypertable. For example, `ALTER TABLE`, and `SELECT`. If the table to convert already contains data, set [migrate\_data](/docs/reference/timescaledb/hypertables/create_hypertable/#arguments/index.md) to `TRUE`. However, this may take a long time and there are limitations when the table contains foreign key constraints. You cannot run `create_hypertable()` on a table that is already partitioned using [declarative partitioning](https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE) or [inheritance](https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-USING-INHERITANCE). The time column 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. This page describes the generalized hypertable API introduced in TimescaleDB v2.13. The [old interface for `create_hypertable` is also available](/docs/reference/timescaledb/hypertables/create_hypertable_old/index.md). ## Samples Before you call `create_hypertable`, you create a standard PostgreSQL relational table. For example: ``` CREATE TABLE conditions ( time TIMESTAMPTZ NOT NULL, location text NOT NULL, temperature DOUBLE PRECISION NULL ); ``` The following examples show you how to create a hypertable from an existing table or a function: - [Time partition a hypertable by time range](/docs/reference/timescaledb/hypertables/create_hypertable/#time-partition-a-hypertable-by-time-range/index.md) - [Time partition a hypertable using composite columns and immutable functions](/docs/reference/timescaledb/hypertables/create_hypertable/#time-partition-a-hypertable-using-composite-columns-and-immutable-functions/index.md) - [Time partition a hypertable using ISO formatting](/docs/reference/timescaledb/hypertables/create_hypertable/#time-partition-a-hypertable-using-iso-formatting/index.md) - [Time partition a hypertable using UUIDv7](/docs/reference/timescaledb/hypertables/create_hypertable/#time-partition-a-hypertable-using-uuidv7/index.md) ### Time partition a hypertable by time range The following examples show different ways to create a hypertable: - Convert with range partitioning on the `time` column: ``` SELECT create_hypertable('conditions', by_range('time')); ``` - Convert with a [set\_chunk\_time\_interval](/docs/reference/timescaledb/hypertables/set_chunk_time_interval/index.md) of 24 hours: Either: ``` SELECT create_hypertable('conditions', by_range('time', 86400000000)); ``` or: ``` SELECT create_hypertable('conditions', by_range('time', INTERVAL '1 day')); ``` - With range partitioning on the `time` column, do not raise a warning if `conditions` is already a hypertable: ``` SELECT create_hypertable('conditions', by_range('time'), if_not_exists => TRUE); ``` If you call `SELECT * FROM create_hypertable(...)` the return value is formatted as a table with column headings. ### Time partition a hypertable using composite columns and immutable functions The following example shows how to time partition the `measurements` relational table on a composite column type using a range partitioning function. 1. Create the report type, then an immutable function that converts 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'; ``` 2. Create the hypertable using the immutable function: ``` SELECT create_hypertable('measurements', by_range('report', partition_func => 'report_reported')); ``` ### Time partition a hypertable using ISO formatting The following example shows how to time partition the `events` table on a `jsonb` (`event`) column type, which has a top level `started` key that contains 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')); ``` ### Time partition a hypertable using UUIDv7 1. Create a table with a UUIDv7 column: - [PostgreSQL 17 and lower](#tab-panel-668) - [PostgreSQL 18](#tab-panel-669) ``` CREATE TABLE events ( id uuid PRIMARY KEY DEFAULT generate_uuidv7(), payload jsonb ); ``` ``` CREATE TABLE events ( id uuid PRIMARY KEY DEFAULT uuidv7(), payload jsonb ); ``` 2. Partition the table based on the timestamps embedded within the UUID values: ``` SELECT create_hypertable( 'events', by_range('id', INTERVAL '1 month') ); ``` Subsequent data insertion and queries automatically leverage the UUIDv7-based partitioning. ## Arguments The syntax for `create_hypertable` is: ``` SELECT create_hypertable( relation = '', dimension = by_range('') | by_hash('', ), create_default_indexes = true | false, if_not_exists = true | false, migrate_data = true | false ); ``` | Name | Type | Default | Required | Description | | ------------------------ | ---------------- | ------- | -------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `create_default_indexes` | `BOOLEAN` | `TRUE` | ✖ | Create default indexes on time/partitioning columns. | | `dimension` | `DIMENSION_INFO` | - | ✔ | To create a `_timescaledb_internal.dimension_info` instance to partition a hypertable, you call [`by_range`](/docs/reference/timescaledb/hypertables/add_dimension/#by_range/index.md) and [`by_hash`](/docs/reference/timescaledb/hypertables/add_dimension/#by_hash/index.md). **Note**: best practice is to not use additional dimensions, especially on Tiger Cloud. | | `if_not_exists` | `BOOLEAN` | `FALSE` | ✖ | Set to `TRUE` to print a warning if `relation` is already a hypertable. By default, an exception is raised. | | `migrate_data` | `BOOLEAN` | `FALSE` | ✖ | Set to `TRUE` to migrate any existing data in `relation` in to chunks in the new hypertable. Depending on the amount of data to be migrated, setting `migrate_data` can lock the table for a significant amount of time. If there are [foreign key constraints](https://docs.tigerdata.com/use-timescale/latest/schema-management/about-constraints/) to other tables in the data to be migrated, `create_hypertable()` can run into deadlock. A hypertable can only contain foreign keys to another hypertable. `UNIQUE` and `PRIMARY` constraints must include the partitioning key. Deadlock may 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. To avoid deadlock, manually obtain a [SHARE ROW EXCLUSIVE](https://www.postgresql.org/docs/current/sql-lock.html) lock on the referenced tables before you call `create_hypertable` in the same transaction. If you leave `migrate_data` set to the default, non-empty tables generate an error when you call `create_hypertable`. | | `relation` | REGCLASS | - | ✔ | Identifier of the table to convert to a hypertable. | ## Returns | Column | Type | Description | | --------------- | ------- | ------------------------------------------------------------------------------------------------------------ | | `hypertable_id` | INTEGER | The ID of the hypertable you created. | | `created` | BOOLEAN | `TRUE` when the hypertable is created. `FALSE` when `if_not_exists` is `true` and no hypertable was created. | On failure, an error is returned: | Error | Description | | --------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------- | | `table "" is already a hypertable` | The table is already a hypertable. Use `if_not_exists => TRUE` to suppress this error. | | `column "" does not exist` | The specified partitioning column does not exist in the table. | | `column "" is already a dimension` | The column is already used as a partitioning dimension. | | `cannot create a unique index without the column "" (used in partitioning)` | Unique and primary key constraints must include all partitioning columns. | | `cannot have FOREIGN KEY constraints to hypertable ""` | Foreign key constraints to hypertables are not supported. | | `cannot create hypertable for table "" because it is part of a publication` | Tables in publications cannot be converted to hypertables. | | `invalid number of partitions: must be between 1 and 32767` | The number of hash partitions specified is out of valid range. | | `cannot specify both the number of partitions and an interval` | When using `by_hash`, specify either the number of partitions or an interval, not both. | | `invalid interval type for dimension` | The chunk interval type does not match the partitioning column type. | | `must be owner of hypertable ""` | Only the table owner can convert it to a hypertable. |