TimescaleDB/PostgreSQL: how to use unique constraint when creating hypertables?

Question

I am trying to create a table in PostgreSQL to contain lots of data and for that reason I want to use timescales hypertable as in the example below.

CREATE TABLE "datapoints" (
  "tstz" timestamptz NOT NULL,
  "id" bigserial UNIQUE NOT NULL,  
  "entity_id" bigint NOT NULL,
  "value" real NOT NULL,
  PRIMARY KEY ("id", "tstz", "entity_id")
);

SELECT create_hypertable('datapoints','tstz');

However, this throws an error - shown below. As far as I have figured out the error arise since the unique constraint isn't allowed in hypertables, but I really need the uniqueness. So does anyone have an idea on how to solve it or work around it?

ERROR:  cannot create a unique index without the column "tstz" (used in partitioning)
SQL state: TS103

Answer 1

There is no way to avoid that.

TimescaleDB uses PostgreSQL partitioning, and it is not possible to have a primary key or unique constraint on a partitioned table that does not contain the partitioning key.

The reason behind that is that an index on a partitioned table consists of individual indexes on the partitions (these are the partitions of the partitioned index). Now the only way to guarantee uniqueness for such a partitioned index is to have the uniqueness implicit in the definition, which is only the case if the partitioning key is part of the index.

So you either have to sacrifice the uniqueness constraint on id (which is pretty much given if you use a sequence) or you have to do without partitioning.