CODEWITHSUNDEEP
sqlpostgresqltimescaledb
huggie
huggie

Reputation: 18247

TimescaleDB cannot create a unique index without the column

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?

Upvotes: 1

Views: 3922

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247410

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.

Upvotes: 0

Related Questions