5

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_...
Juanito Fatas
  • 9,419
  • 9
  • 46
  • 70
Suman Das
  • 301
  • 1
  • 4
  • 18

2 Answers2

10

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

Juanito Fatas
  • 9,419
  • 9
  • 46
  • 70
Mike Freedman
  • 1,692
  • 9
  • 9
  • 2
    @mike so how could one have a unique column used as a foreign key by another table under this constraint? – JeffCharter Nov 24 '19 at 19:00
  • @JeffCharter 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. – Mike Freedman May 03 '20 at 13:08
6

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.

selivandex
  • 85
  • 1
  • 5
  • 5
    this makes no sense, with that index nothing will prevent inserting two rows with the same id but a different timestamp. – singe3 Aug 30 '20 at 19:32
  • 1
    There is a gem called https://github.com/composite-primary-keys/composite_primary_keys which supports this if you need it. – Bryan Clark Sep 09 '21 at 21:23
  • @singe3 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) – baxx Jul 08 '23 at 23:17