> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# create_hypertable() (old interface)
> Create a hypertable
export const TIMESCALE_DB = 'TimescaleDB';
export const PG = 'Postgres';
export const HYPERTABLE_CAP = 'Hypertable';
export const HYPERTABLE = 'hypertable';
export const CHUNK = 'chunk';
Old API [v2.13.0][tsdb-2.13.0]. Use [`create_hypertable`][api-create-hypertable]. Since [1.0.0][tsdb-1.0.0]
Creates a {TIMESCALE_DB} {HYPERTABLE} from a {PG} table (replacing the latter),
partitioned on time and with the option to partition on one or more other
columns. The {PG} 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 {HYPERTABLE}s, including chunk size partitioning,
see the [hypertable documentation][hypertable-docs].
## Samples
Convert table `conditions` to {HYPERTABLE} with just time partitioning on column `time`:
```sql theme={"dark"}
SELECT create_hypertable('conditions', 'time');
```
Convert table `conditions` to {HYPERTABLE}, setting `chunk_time_interval` to 24 hours.
```sql theme={"dark"}
SELECT create_hypertable('conditions', 'time', chunk_time_interval => 86400000000);
SELECT create_hypertable('conditions', 'time', chunk_time_interval => INTERVAL '1 day');
```
Convert table `conditions` to {HYPERTABLE}. Do not raise a warning
if `conditions` is already a {HYPERTABLE}:
```sql theme={"dark"}
SELECT create_hypertable('conditions', 'time', if_not_exists => TRUE);
```
Time partition table `measurements` on a composite column type `report` using a
time partitioning function. Requires an immutable function that can convert the
column value into a supported column value:
```sql theme={"dark"}
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', 'report', time_partitioning_func => 'report_reported');
```
Time partition table `events`, on a column type `jsonb` (`event`), which has
a top level key (`started`) containing an ISO 8601 formatted timestamp:
```sql theme={"dark"}
CREATE FUNCTION event_started(jsonb)
RETURNS timestamptz
LANGUAGE SQL
IMMUTABLE AS
$func$SELECT ($1->>'started')::timestamptz$func$;
SELECT create_hypertable('events', 'event', time_partitioning_func => 'event_started');
```
## Arguments
The syntax is:
```sql theme={"dark"}
SELECT create_hypertable(
relation = '',
time_column_name = '',
partitioning_column = '',
number_partitions = ,
associated_schema_name = '',
associated_table_prefix = '',
chunk_time_interval = ,
create_default_indexes = true | false,
if_not_exists = true | false,
partitioning_func = '',
migrate_data = true | false,
chunk_target_size = '',
chunk_sizing_func = '',
time_partitioning_func = ''
);
```
| Name | Type | Default | Required | Description |
| ------------------------- | -------- | ----------------------- | -------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `relation` | REGCLASS | - | ✔ | Identifier of table to convert to {HYPERTABLE}. |
| `time_column_name` | REGCLASS | - | ✔ | Name of the column containing time values as well as the primary column to partition by. |
| `partitioning_column` | REGCLASS | - | ✖ | Name of an additional column to partition by. If provided, the `number_partitions` argument must also be provided. |
| `number_partitions` | INTEGER | - | ✖ | Number of hash partitions to use for `partitioning_column`. Must be > 0. |
| `chunk_time_interval` | INTERVAL | `7 days` | ✖ | Event time that each chunk covers. Must be > 0. Default is 7 days. |
| `create_default_indexes` | BOOLEAN | `true` | ✖ | Whether to create default indexes on time/partitioning columns. Default is TRUE. |
| `if_not_exists` | BOOLEAN | `false` | ✖ | Whether to print warning if table already converted to {HYPERTABLE} or raise exception. Default is FALSE. |
| `partitioning_func` | REGCLASS | - | ✖ | The function to use for calculating a value's partition. |
| `associated_schema_name` | REGCLASS | `_timescaledb_internal` | ✖ | Name of the schema for internal {HYPERTABLE} tables. Default is `_timescaledb_internal`. |
| `associated_table_prefix` | TEXT | `_hyper` | ✖ | Prefix for internal {HYPERTABLE} chunk names. Default is `_hyper`. |
| `migrate_data` | BOOLEAN | `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. |
| `time_partitioning_func` | REGCLASS | - | ✖ | Function to convert incompatible primary time column values to compatible ones. The function must be `IMMUTABLE`. |
| `replication_factor` | INTEGER | - | ✖ | Replication factor to use with distributed {HYPERTABLE}. If not provided, value is determined by the `timescaledb.hypertable_replication_factor_default` GUC. |
| `data_nodes` | ARRAY | - | ✖ | This is the set of data nodes that are used for this table if it is distributed. This has no impact on non-distributed {HYPERTABLE}s. If no data nodes are specified, a distributed {HYPERTABLE} uses all data nodes known by this instance. |
| `distributed` | BOOLEAN | `NULL` | ✖ | Set to TRUE to create distributed {HYPERTABLE}. If not provided, value is determined by the `timescaledb.hypertable_distributed_default` GUC. When creating a distributed {HYPERTABLE}, consider using [`create_distributed_hypertable`][create_distributed_hypertable] in place of `create_hypertable`. Default is NULL. |
## Returns
| Column | Type | Description |
| --------------- | ------- | --------------------------------------------------------------------------------------------------------- |
| `hypertable_id` | INTEGER | ID of the {HYPERTABLE} in {TIMESCALE_DB}. |
| `schema_name` | TEXT | Schema name of the table converted to {HYPERTABLE}. |
| `table_name` | TEXT | Table name of the table converted to {HYPERTABLE}. |
| `created` | BOOLEAN | 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.
On failure, an error is returned:
| Error | Description |
| -------------------------------------------------------------------- | --------------------------------------------------------------------------- |
| {HYPERTABLE} "{table_name}" not found | The specified table does not exist |
| permission denied for schema {schema_name} | Insufficient permissions to access the schema |
| must be owner of {HYPERTABLE} "{table_name}" | Only the table owner can convert it to a {HYPERTABLE} |
| permissions denied: cannot create {CHUNK}s in schema "{schema_name}" | Insufficient permissions on the associated schema for {CHUNK}s |
| table "{table_name}" is already a {HYPERTABLE} | The table has already been converted to a {HYPERTABLE} |
| table "{table_name}" is not empty | The table contains data and `migrate_data` is not set to true |
| invalid partitioning function | The specified partitioning function is not valid or has incorrect signature |
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,
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
obtaining `SHARE ROW EXCLUSIVE` lock on the referenced tables before calling
`create_hypertable` in the same transaction, see
[{PG} documentation][pg-documentation]
for the syntax.
## Units
The `time` column supports the following data types:
| Description | Types |
| ----------- | ---------------------- |
| Timestamp | TIMESTAMP, TIMESTAMPTZ |
| Date | DATE |
| Integer | SMALLINT, INT, BIGINT |
The type flexibility of the 'time' column allows the use of non-time-based
values as the primary chunk partitioning column, as long as those values can
increment.
For incompatible data types (for example, `jsonb`) you can specify a function to
the `time_partitioning_func` argument which can extract a compatible data type.
The units of `chunk_time_interval` should be set as follows:
* For time columns having timestamp or DATE types, the `chunk_time_interval`
should be specified either as an `interval` type or an integral value in
*microseconds*.
* For integer types, the `chunk_time_interval` **must** be set explicitly, as
the database does not otherwise understand the semantics of what each
integer value represents (a second, millisecond, nanosecond, etc.). So if
your time column is the number of milliseconds since the UNIX epoch, and you
wish to have each chunk cover 1 day, you should specify
`chunk_time_interval => 86400000`.
In case of hash partitioning (in other words, if `number_partitions` is greater
than zero), it is possible to optionally specify a custom partitioning function.
If no custom partitioning function is specified, the default partitioning
function is used. The default partitioning function calls {PG}'s internal
hash function for the given type, if one exists. Thus, a custom partitioning
function can be used for value types that do not have a native {PG} hash
function. A partitioning function should take a single `anyelement` type
argument and return a positive `integer` hash value. Note that this hash value
is *not* a partition ID, but rather the inserted value's position in the
dimension's key space, which is then divided across the partitions.
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.
[api-create-hypertable]: /api-reference/timescaledb/hypertables/create_hypertable
[create_distributed_hypertable]: /api-reference/timescaledb/distributed-hypertables/create_distributed_hypertable
[hypertable-docs]: /manage-data/capabilities/hypertables/understand-hypertables
[pg-documentation]: https://www.postgresql.org/docs/current/sql-lock.html
[tsdb-1.0.0]: https://github.com/timescale/timescaledb/releases/tag/1.0.0
[tsdb-2.13.0]: https://github.com/timescale/timescaledb/releases/tag/2.13.0