Create hypertables

Note

This code uses the new generalized hypertable API introduced in TimescaleDB 2.13. The old interface for create_hypertable is also available.

After creating a Timescale database, you're ready to create your first hypertable. Creating a hypertable is a two-step process:

1. Create a PostgreSQL table as usual
2. Convert it to a hypertable

You can also create distributed hypertables. For more information, see the distributed hypertables section.

To create a hypertable, you need to create a standard PostgreSQL table, and then convert it into a hypertable.

Hypertables are intended for time-series data, so your table needs a column that holds time values. This can be a timestamptz, date, or integer. Ensure that you set the datatype for the time column as timestamptz and not timestamp. For more information, see PostgreSQL timestamp.

Creating a hypertable

1. Create a standard PostgreSQL table:

   CREATE TABLE conditions (
      time        TIMESTAMPTZ       NOT NULL,
      location    TEXT              NOT NULL,
      device      TEXT              NOT NULL,
      temperature DOUBLE PRECISION  NULL,
      humidity    DOUBLE PRECISION  NULL
   );

   Copy

2. Convert the table to a hypertable. Specify the name of the table you want to convert, and the column that holds its time values.

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

   Copy

Note

If your table already has data, you can migrate the data when creating the hypertable. Set the migrate_data argument to true when you call the create_hypertable function. This might take a long time if you have a lot of data. For more information about migrating data, see the Migrate your data to Timescale Cloud.