TimescaleDB cannot create a unique index without the column

Question

I'm using Timescaledb version 2.3.0 with freshly created tables. I have the following table (redacted):

local=# \d+ dock_status;
                                               Table "public.dock_status"
       Column       |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description
--------------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
 id                 | uuid                     |           | not null |         | plain    |              |
 dock_id            | uuid                     |           |          |         | plain    |              |
 created_at         | timestamp with time zone |           | not null |         | plain    |              |
 updated_at         | timestamp with time zone |           |          |         | plain    |              |
 deleted_at         | timestamp with time zone |           |          |         | plain    |              |
 is_pwr_btn_enabled | boolean                  |           |          |         | plain    |              |
Indexes:
    "dock_status_pkey" PRIMARY KEY, btree (id)
    "unique_index_dock_status_created_at" UNIQUE, btree (created_at)
Access method: heap

I attempt to create hypertable by:

SELECT create_hypertable('dock_status', 'created_at', migrate_data => true, if_not_exists => true);

The error message is:

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

According to this, I should create an unique index for created_at, which I did. So what went wrong?

Does this answer help? https://stackoverflow.com/a/66041772/840340 It is mentioned there that it is not required to have unique index and if you need then you need to include the time column. — k_rus, Jun 22 '21 at 08:58

score -1 · Answer 1 · answered Jun 21 '21 at 10:46

A "hypertable" is a thin layer around PostgreSQL partitioning.

An index on a partitioned table is a partitioned index – it consists of an index on each table partition. The only way such an index can be globally unique is if it is unique by definition, that is, contains the partitioning key as an index column.

This is a PostgreSQL limitation, but you should be ready to pay that price for the benefits of partitioning. A "global index" which could support globally unique indexes would take a lot of fun out of partitioning.

TimescaleDB cannot create a unique index without the column

1 Answers1