Enforce constraints with unique indexes

Determine the partitioning columns

Before you create a unique index, you need to determine which unique indexes are allowed on your hypertable. Begin by identifying your partitioning columns.

TimescaleDB traditionally uses the following columns to partition hypertables:

• The time column used to create the hypertable. Every Timescale hypertable is partitioned by time.
• Any space-partitioning columns. Space partitions are optional and not included in every hypertable.

Create a hypertable

Create a hypertable for your time-series data using CREATE TABLE. For efficient queries on data in the columnstore, remember to segmentby the column you will use most often to filter your data. For example:

CREATE TABLE hypertable_example(
  time TIMESTAMPTZ,
  user_id BIGINT,
  device_id BIGINT,
  value FLOAT
) WITH (
  tsdb.hypertable,
  tsdb.partition_column='time',
  tsdb.segmentby = 'device_id',
  tsdb.orderby = 'time DESC'
);

Copy

If you are self-hosting TimescaleDB v2.19.3 and below, create a PostgreSQL relational table, then convert it using create_hypertable. You then enable hypercore with a call to ALTER TABLE.

Create a unique index on the hypertable

When you create a unique index on a hypertable, it must contain all the partitioning columns. It may contain other columns as well, and they may be arranged in any order. You cannot create a unique index without time, because time is a partitioning column.

For example:

• Create a unique index on time and device_id with a call to CREATE UNIQUE INDEX:

  CREATE UNIQUE INDEX idx_deviceid_time
    ON hypertable_example(device_id, time);

  Copy

• Create a unique index on time, user_id, and device_id.

  device_id is not a partitioning column, but this still works:

  CREATE UNIQUE INDEX idx_userid_deviceid_time
    ON hypertable_example(user_id, device_id, time);

  Copy

Note

This restriction is necessary to guarantee global uniqueness in the index.

Create a relational table

CREATE TABLE another_hypertable_example(
  time TIMESTAMPTZ,
  user_id BIGINT,
  device_id BIGINT,
  value FLOAT
);

Copy

Create a unique index on the table

For example, on device_id and time:

CREATE UNIQUE INDEX idx_deviceid_time
  ON another_hypertable_example(device_id, time);

Copy

Turn the table into a partitioned hypertable

• On time alone:

  SELECT * from create_hypertable('another_hypertable_example', by_range('time'));

  Copy

• On time and device_id:

  SELECT * FROM create_hypertable('another_hypertable_example', by_range('time'));
  SELECT * FROM add_dimension('another_hypertable_example', by_hash('device_id', 4));

  Copy

You get an error if you try to turn the relational table into a hypertable partitioned by time and user_id. This is because user_id is not part of the UNIQUE INDEX. To fix the error, add user_id to your unique index.