如何使用 created_at 将简单的 postgresql 表转换为 hypertable 或 timescale db 表进行索引 [英] How to convert a simple postgresql table to hypertable or timescale db table using created_at for indexing
问题描述
问题是，当我想使用 created_at 表字段进行索引将简单的 Postgresql 表转换为 timescaledb 表或 hypertable 时，它会显示此错误.表名是订单.这里 cas_admin_db_new 是数据库名称.
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_...
推荐答案
Timescale 人在这里.问题是您的架构可能将其他一些列列为主键(或 UNIQUE 索引).
Timescale person here. The issue is that your schema probably lists some other column as a primary key (or UNIQUE index).
TimescaleDB 要求任何 PK/唯一索引都包括所有分区键，在您的情况下为 created_at.
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.
更多信息:
这篇关于如何使用 created_at 将简单的 postgresql 表转换为 hypertable 或 timescale db 表进行索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
