> ## 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.

# Enforce constraints with unique indexes

> Having a unique index on your hypertable simplifies lookup, speeds up aggregation, and makes JOINs more efficient. Learn to create a unique index in TimescaleDB and what the related limitations are

export const PG = 'Postgres';

export const CHUNK = 'chunk';

export const COLUMNSTORE = 'columnstore';

export const TIMESCALE_DB = 'TimescaleDB';

export const HYPERTABLE = 'hypertable';

You use unique indexes on a {HYPERTABLE} to enforce [constraints][postgres-createconstraint]. If you have a primary key,
you have a unique index. In {PG}, a primary key is a unique index with a `NOT NULL` constraint.

You do not need to have a unique index on your {HYPERTABLE}s. 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.

   {TIMESCALE_DB} traditionally uses the following columns to partition {HYPERTABLE}s:

   * The `time` column used to create the {HYPERTABLE}. Every {TIMESCALE_DB} {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}][hypertables-section] for your time-series data using [`CREATE TABLE`][hypertable-create-table].
   For [efficient queries][secondary-indexes] on data in the columnstore, remember to `segmentby` the column you will
   use most often to filter your data. For example:

   ```sql theme={"dark"}
   CREATE TABLE hypertable_example(
     time TIMESTAMPTZ,
     user_id BIGINT,
     device_id BIGINT,
     value FLOAT
   ) WITH (
     timescaledb.hypertable,
     timescaledb.segmentby = 'device_id',
     timescaledb.orderby = 'time DESC'
   );
   ```

   When you create a {HYPERTABLE} using [`CREATE TABLE ... WITH ...`][hypertable-create-table], the default partitioning
   column is automatically the first column with a timestamp data type. Also, {TIMESCALE_DB} creates a
   [columnstore policy][add_columnstore_policy] that automatically converts your data to the {COLUMNSTORE}, after an
   interval equal to the value of the [`chunk_interval`][create_table_arguments], defined through `compress_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} {CHUNK}s are compressed by
   up to 98%, and organized for efficient, large-scale queries.

   You can customize this policy later using [`alter_job`][alter_job_samples]. However, to change `after` or
   `created_before`, the compression settings, or the {HYPERTABLE} the policy is acting on, you must
   [remove the columnstore policy][remove_columnstore_policy] and [add a new one][add_columnstore_policy].

   You can also manually [convert {CHUNK}s][convert_to_columnstore] in a {HYPERTABLE} to the {COLUMNSTORE}.

   [add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy

   [alter_job_samples]: /api-reference/timescaledb/jobs-automation/alter_job#samples

   [convert_to_columnstore]: /api-reference/timescaledb/hypercore/convert_to_columnstore

   [create_table_arguments]: /api-reference/timescaledb/hypertables/create_table#arguments

   [hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table

   [remove_columnstore_policy]: /api-reference/timescaledb/hypercore/remove_columnstore_policy

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`:

     ```sql theme={"dark"}
     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:

     ```sql theme={"dark"}
     CREATE UNIQUE INDEX idx_userid_deviceid_time
       ON hypertable_example(user_id, device_id, time);
     ```

   <Info>
     This restriction is necessary to guarantee global uniqueness in the index.
   </Info>

## 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**

   ```sql theme={"dark"}
   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`:

   ```sql theme={"dark"}
   CREATE UNIQUE INDEX idx_deviceid_time
     ON another_hypertable_example(device_id, time);
   ```

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

   * On `time` alone:

     ```sql theme={"dark"}
     SELECT * from create_hypertable('another_hypertable_example', by_range('time'));
     ```

   * On `time` and `device_id`:

     ```sql theme={"dark"}
     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.

[hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table

[hypertables-section]: /manage-data/capabilities/hypertables/understand-hypertables

[postgres-createconstraint]: https://www.postgresql.org/docs/current/ddl-constraints.html

[secondary-indexes]: /manage-data/capabilities/hypercore/secondary-indexes