How to convert a simple postgresql table to hypertable or timescale db table using created_at for indexing

Question

The problem is that when I want to convert a simple Postgresql table to timescaledb table or hypertable using created_at table field for indexing then it will show this error. The table name is orders. Here cas_admin_db_new is the databse name.

I have tried all the possible way. which is bellow but the orders table doesn't convert into hypertable.

SELECT create_hypertable('orders','created_at', chunk_time_interval => 6040800000000);
ERROR: cannot create a unique index without the column "created_at" (used in partitioning)

SELECT create_hypertable('public.orders','created_at', chunk_time_interval => 6040800000000);
ERROR: cannot create a unique index without the column "created_at" (used in partitioning)

cas_admin_db_new=# SELECT create_hypertable('public.orders','created_at', chunk_time_interval => 6040800000000, created_default_indexes=>FALSE);
ERROR: function create_hypertable(unknown, unknown, chunk_time_interval => bigint, created_default_indexes => boolean) does not exist

cas_admin_db_new=# SELECT create_hypertable('"ORDER"','created_at', chunk_time_interval => 6040800000000);
ERROR: relation "ORDER" does not exist
LINE 1: SELECT create_hypertable('"ORDER"','created_at', chunk_time_...

Jargonize

Answer 1

Timescale person here. The issue is that your schema probably lists some other column as a primary key (or UNIQUE index).

TimescaleDB requires that any PK/unique index includes all partitioning keys, in your case, created_at.

That's because we do this heavy underlying partitioning, and don't want to build global lookup structures to ensure uniqueness outside of what we already use for partitioning.

More info:

https://docs.timescale.com/timescaledb/latest/how-to-guides/schema-management/indexing/##best-practices

Larianna

@mike so how could one have a unique column used as a foreign key by another table under this constraint? – Goethe

@Goethe Uniqueness doesn't require an ID; we see many users create UNIQUE keys on something like: (timestamp, device_id), i.e., each device should only be providing a single record per unique timestamp. FKs are a different topic/question; TimescaleDB currently supports FKs out of hypertables to other relational tables, but not FKs from tables into hypertables. In the future that could be relaxed provided that the hypertable column is UNIQUE. – Larianna

Answer 2

You need to drop your current primary key on table and create new composite primary key like so:

ALTER TABLE table_name ADD PRIMARY KEY (id, created_at);

But there is problem: Unfortunately ActiveRecord doesn't support composite primary key.

Intoxicated

this makes no sense, with that index nothing will prevent inserting two rows with the same id but a different timestamp. – Kahle

There is a gem called github.com/composite-primary-keys/composite_primary_keys which supports this if you need it. – Petterson

@Kahle typically - to my understanding - a hypertable would typically be something like a large fact table where there might be many entries with the same ID (eg sensor data from the same sensor across time) – Circumspect