How to create unique index on only id field?

TimescaleDB and PostgreSQL
1

Hi, Dear TSDB Team and users!
I want to implement unique values for id column. But TS throws an exception even when there is already one restriction, including the date of chunking.

CREATE TABLE hypertable (
	id numeric NOT NULL,
	chunk_date timestamp NOT NULL,
	val smallint,
	PRIMARY KEY (chunk_date, id)
);
CREATE UNIQUE INDEX ON hypertable USING btree (id);

SELECT * FROM create_hypertable('hypertable', 'chunk_date', chunk_time_interval => INTERVAL '1 month');

Output:
SQL Error [TS103]: ERROR: cannot create a unique index without the column "modify_date" (used in partitioning)

With this approach, there will inevitably be duplicates in the id field, which must be avoided.

2

Hi Lux, there’s no way to make it without combining with the timestamp. I guess the failing line is on CREATE UNIQUE INDEX right? Have you checked the best practices for indexes?

1 Like
3

Yes, you are right.

Thank you, @jonatasdp . Yes, I read those best practices. It is pitty, that it is no way to create such indexed(

4

The problem of unique index is that you’d need to check the uniqueness consistency across all the chunks. It would become a very expensive task if we don’t tie it to the timestamp which would scope it in a single chunk. As far as I understand this is the main reason.

Just curious, why combining it with the timestamp field does not work for your case?

5

It is banal situation) It is needed to keep uniquness by id. But chunk date is different for the same id every time and this creates duplicates of ids.
At now I recreated the table into regular postgresql table.