Create unique indexes on a hypertable

To create a unique index on a hypertable:

  1. Determine your partitioning columns
  2. Create a unique index that includes all those columns, and optionally additional columns
note

This section pertains only to unique indexes, not to indexes in general. You can create a hypertable without any unique index, though you might want one to enforce constraints.

If you have a primary key, you have a unique index. In PostgreSQL, a primary key is a unique index with a constraint.

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

TimescaleDB uses these columns to partition hypertables:

  • Any space-partitioning columns. Space partitions are optional and not included in every hypertable. You have a space-partitioning column if you specify a partitioning_column parameter when you call or create_distributed_hypertable.

When you create a unique index on a hypertable, it must contain all the partitioning columns you identified earlier. It may contain other columns as well, and they may be arranged in any order.

note

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

Create a unique index as you normally would in PostgreSQL, using CREATE UNIQUE INDEX. Make sure to include all partitioning columns in the index. You can include other columns as well if needed.

You can also create a unique index on time, user_id, and device_id. Note that device_id is not a partitioning column, but this still works:

  2. CREATE UNIQUE INDEX idx_userid_deviceid_time
note

You cannot create a unique index without time, because time is a partitioning column. This does not work:

-- This gives you an error CREATE UNIQUE INDEX idx_deviceid ON hypertable_example(device_id);

You get the error:

ERROR: cannot create a unique index without the column "<COLUMN_NAME>" (used in partitioning)

Fix the error by adding time to your unique index.

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 your table. For example:

    1. CREATE UNIQUE INDEX idx_deviceid_time
    3.   ON hypertable_example(device_id, time);
note

You cannot turn the table into a hypertable partitioned by time and user_id, because user_id isn’t part of the unique index. This doesn’t work:

-- This gives you an error SELECT * FROM create_hypertable( 'hypertable_example', 'time', partitioning_column => 'user_id', number_partitions => 4 );

You get the error:

ERROR: cannot create a unique index without the column "<COLUMN_NAME>" (used in partitioning)

Note that the error arises from creating an index, not from creating a hypertable. This happens because TimescaleDB recreates indexes after converting a table to a hypertable.

Fix the error by adding to your unique index.