postgresql - 错误 : cannot create a unique index without the column "date_time" (used in partitioning)

Question

我刚开始使用timescaleDB用postgresql。我有一个名为 storage_db 的数据库其中包含一个名为 day_ahead_prices 的表.

安装 timescaledb 后，我关注 Migrate from the same postgresql database迁移我的 storage_db进入时间刻度数据库。

当我这样做时(包括索引):

CREATE TABLE tsdb_day_ahead_prices (LIKE day_ahead_prices INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);
select create_hypertable('tsdb_day_ahead_prices', 'date_time');

它给了我以下错误:

ERROR:  cannot create a unique index without the column "date_time" (used in partitioning)

但是当我这样做时(索引排除):

CREATE TABLE tsdb_day_ahead_prices (LIKE day_ahead_prices INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);
select create_hypertable('tsdb_day_ahead_prices', 'date_time');

它是成功的。之后，我做了

select create_hypertable('tsdb_day_ahead_prices', 'date_time');

它给了我以下输出:

         create_hypertable          
------------------------------------
 (3,public,tsdb_day_ahead_prices,t)
(1 row)

我对此有点陌生，所以任何人都可以向我解释它们之间的区别是什么，为什么我在第一种情况下会出错？

PS:

我的 day_ahead_prices如下所示:

 id | country_code | values  |         date_time          
----+--------------+---------+----------------------------
  1 | LU           | 100.503 | 2020-04-11 14:04:30.461605
  2 | LU           | 100.503 | 2020-04-11 14:18:39.600574
  3 | DE           |  106.68 | 2020-04-11 15:59:10.223965

编辑 1:

我创建了 day_ahead_prices python 中的表使用 flask和 flask_sqlalchemy代码是:

class day_ahead_prices(db.Model):
    __tablename__ = "day_ahead_prices"
    id = db.Column(db.Integer, primary_key=True)
    country_code = db.Column(avail_cc_enum, nullable=False)
    values = db.Column(db.Float(precision=2), nullable=False)
    date_time = db.Column(db.DateTime, default=datetime.now(tz=tz), nullable=False)

    def __init__(self, country_code, values):
        self.country_code = country_code
        self.values = values

Answer 1

执行 CREATE TABLE tsdb_day_ahead_prices (LIKE day_ahead_prices INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES); 时你告诉数据库创建 tsdb_day_ahead_prices使用 day_ahead_prices 的表作为模板(相同的列，这些列的相同类型)，但您还告诉它包含您在原始表上定义的默认值、约束和索引，并为您的新表应用/创建相同的值。

然后执行 timescaledb 命令，使 tsdb_day_ahead_prices table
超表。超表是隐藏物理分区的抽象
table 。 (https://www.timescale.com/products/how-it-works)。你在说
TimescaleDB 制作 tsdb_day_ahead_prices使用 date_time 的超表列作为分区键。

创建超表时，TimescaleDB 强加的一个约束是分区列(在您的情况下为“date_time”)必须包含在该表的任何唯一索引(和主键)中。 (https://docs.timescale.com/latest/using-timescaledb/schema-management#indexing-best-practices)

你得到的第一个错误 cannot create a unique index without the column "date_time"正是因为如此。您复制了 id 上的主键定义柱子。所以主键是防止
该表是一个超表。

第二次，您创建了 tsdb_day_ahead_prices表，但你没有复制
原始表中的索引，因此未定义主键(这实际上是唯一索引)。这样 super 表的创建就成功了。

从 create_hypertable 函数获得的输出告诉您，您有一个新的超表，在公共(public)模式中，超表的名称，以及 timescaledb 用于它的内部 id。

所以现在你可以使用 tsdb_day_ahead_prices像往常一样，下面的 timescaledb 将确保数据进入正确的分区/ block

此表的 id 是否需要是唯一的？
如果您要保留时间序列数据
那么每一行对于每个 id 可能并不是真正唯一的，但可能在给定时间由 id 唯一标识。

您可以为要识别的项目创建一个单独的表items(id PRIMARY KEY, country_code)并有超表是day_ahead_prices(time, value, item_id REFERENCES items(id))