---
title: Enforce constraints with unique indexes | Tiger Data Docs
description: Having a unique index on your hypertable simplifies lookup, speeds up aggregation, and makes JOINs more efficient. Learn to create a unique index and what the related limitations are.
---

You use unique indexes on a hypertable to enforce [constraints](https://www.postgresql.org/docs/current/ddl-constraints.html). If you have a primary key, you have a unique index. In PostgreSQL, a primary key is a unique index with a `NOT NULL` constraint.

You do not need to have a unique index on your hypertables. When you create a unique index, it must contain all the partitioning columns of the hypertable.

## Create a hypertable and add unique indexes

To create a unique index on a hypertable:

1. **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 TimescaleDB hypertable is partitioned by time.
   - Any space-partitioning columns. Space partitions are optional and not included in every hypertable.

2. **Create a hypertable**

   Create a [hypertable](/docs/learn/hypertables/understand-hypertables/index.md) for your time-series data using [CREATE TABLE](/docs/reference/timescaledb/hypertables/create_table/index.md). For [efficient queries](/docs/build/performance-optimization/secondary-indexes/index.md) 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.segmentby = 'device_id',
     tsdb.orderby = 'time DESC'
   );
   ```

   When you create a hypertable using [CREATE TABLE … WITH …](/docs/reference/timescaledb/hypertables/create_table/index.md), the default partitioning column is automatically the first column with a timestamp data type. Also, TimescaleDB creates a [columnstore policy](/docs/reference/timescaledb/hypercore/add_columnstore_policy/index.md) that automatically converts your data to the columnstore, after an interval equal to the value of the [chunk\_interval](/docs/reference/timescaledb/hypertables/set_chunk_time_interval/index.md), defined through `after` in the policy. This columnar format enables fast scanning and aggregation, optimizing performance for analytical workloads while also saving significant storage space. In the columnstore conversion, hypertable chunks are compressed by up to 98%, and organized for efficient, large-scale queries.

   You can customize this policy later using [alter\_job](/docs/reference/timescaledb/jobs-automation/alter_job/index.md). However, to change `after` or `created_before`, the compression settings, or the hypertable the policy is acting on, you must [remove the columnstore policy](/docs/reference/timescaledb/hypercore/remove_columnstore_policy/index.md) and [add a new one](/docs/reference/timescaledb/hypercore/add_columnstore_policy/index.md).

   You can also manually [convert chunks](/docs/reference/timescaledb/hypercore/convert_to_columnstore/index.md) in a hypertable to the columnstore.

3. **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);
     ```

   - 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);
     ```

   Note

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

## Create a hypertable from an existing table with unique indexes

If you create a unique index on a table before turning it into a hypertable, the same restrictions apply in reverse. You can only partition the table by columns in your unique index.

1. **Create a relational table**

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

2. **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);
   ```

3. **Turn the table into a partitioned hypertable**

   - On `time` alone:

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

   - 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));
     ```

   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.