如何在TimescaleDB中更新唯一域名的时间值且不生成重复行？

你当前的核心问题是联合唯一约束(domain, requeue_at)和需求冲突——这个约束允许同一个域名对应不同的requeue_at值，自然会出现重复域名的行。下面分两种场景给出解决方案：

场景1：不需要保留历史变更记录，仅需每个域名的最新状态 #

TimescaleDB的超表要求唯一约束必须包含分区键，而你要的domain唯一约束不包含分区键requeue_at，这种情况下没法直接用超表，更适合用普通PostgreSQL表实现需求：

1. 创建表结构（domain设为主键，天然唯一） #

CREATE TABLE domain_rechecks (
    domain VARCHAR PRIMARY KEY,
    requeue_at TIMESTAMP NOT NULL,
    requeued_at TIMESTAMP
);

-- 可选：给requeue_at加索引，优化按时间查询的性能
CREATE INDEX idx_requeue_at ON domain_rechecks(requeue_at);

2. 用Upsert实现插入/更新 #

-- 插入新域名，或更新已有域名的时间值
INSERT INTO domain_rechecks (domain, requeue_at, requeued_at)
VALUES ('google.com', '2024-05-20 14:30:00', NOW())
ON CONFLICT (domain)
DO UPDATE SET
    requeue_at = EXCLUDED.requeue_at,
    requeued_at = EXCLUDED.requeued_at;

场景2：需要保留历史变更记录，同时用TimescaleDB管理时间序列 #

拆分两个表，一个存最新状态，一个用TimescaleDB超表存历史变更：

1. 创建最新状态表（domain唯一） #

CREATE TABLE domain_latest_rechecks (
    domain VARCHAR PRIMARY KEY,
    requeue_at TIMESTAMP NOT NULL,
    requeued_at TIMESTAMP
);

2. 创建历史记录超表（按变更时间分区） #

CREATE TABLE domain_recheck_history (
    domain VARCHAR NOT NULL,
    requeue_at TIMESTAMP NOT NULL,
    requeued_at TIMESTAMP,
    changed_at TIMESTAMP DEFAULT NOW() NOT NULL
);
-- 注意：表名要和建表语句一致，你原代码里的`domain__rechecks`是笔误
SELECT create_hypertable('domain_recheck_history', 'changed_at');

3. 触发器自动记录历史变更 #

-- 编写触发器函数：更新最新状态时，把旧数据插入历史表
CREATE OR REPLACE FUNCTION log_recheck_history()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO domain_recheck_history (domain, requeue_at, requeued_at)
    VALUES (OLD.domain, OLD.requeue_at, OLD.requeued_at);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到最新状态表
CREATE TRIGGER trigger_recheck_history
BEFORE UPDATE ON domain_latest_rechecks
FOR EACH ROW EXECUTE FUNCTION log_recheck_history();

4. 执行Upsert操作 #

INSERT INTO domain_latest_rechecks (domain, requeue_at, requeued_at)
VALUES ('cnn.com', '2024-05-21 09:00:00', NULL)
ON CONFLICT (domain)
DO UPDATE SET
    requeue_at = EXCLUDED.requeue_at,
    requeued_at = EXCLUDED.requeued_at;

内容的提问来源于stack exchange，提问作者Javed Ahamed