PostgreSQL仅插入与旧值不同的新数据问题求助

场景说明 #

我有一张存储遥测数据的modbus表，数据结构及示例如下：

time         |     name     | slave_id |       type       | fCounter
---------------------+--------------+----------+------------------+----------
 2024-05-14 11:20:30| FoobarCounter | 1        | holding_register |      101
 2024-05-14 11:20:20| FoobarCounter | 1        | holding_register |      101
 2024-05-14 11:20:10| FoobarCounter | 1        | holding_register |      100
 2024-05-14 11:20:00| FoobarCounter | 1        | holding_register |      100

Telegraf会每10秒往这个表插入一条新记录，但fCounter字段的实际测量值只会每数小时更新一次。我希望只把值发生变化的记录（包含对应的时间）存储到新表foobar_counter中，避免冗余插入。

我的尝试及报错 #

首先创建了目标表：

CREATE TABLE foobar_counter (
    time timestamp without time zone,
    value bigint NOT NULL
);

接着创建插入函数：

CREATE OR REPLACE FUNCTION insert_function(timestamp without time zone, text, text, text, bigint)
    RETURNS VOID
    AS $$ INSERT INTO foobar_counter VALUES  ($1, $5) $$
    LANGUAGE SQL;

然后创建触发器：

CREATE OR REPLACE TRIGGER insert_trigger
    BEFORE INSERT ON modbus
    FOR EACH ROW
    WHEN (foobar_counter.value IS DISTINCT FROM NEW."fCounter")
    EXECUTE FUNCTION insert_function();

执行后触发错误：

ERROR:  invalid reference to FROM-clause entry for table "foobar_counter"
LINE 4:     WHEN (foobar_counter.value IS DISTINCT FROM NEW."fCounter...

更新说明 #

原本以为fCounter只会递增，用唯一索引方案就能满足需求，但现在发现计数器可能会重置为0，因此"新值"的判断标准是与目标表中最后一条记录的值不同。

PostgreSQL版本：PostgreSQL 15.6 (Debian 15.6-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

解决方案 #

触发器的WHEN子句只能引用当前触发行的NEW/OLD数据，无法直接查询其他表，因此需要把判断逻辑放到触发器函数内部：

1. 创建带判断逻辑的触发器函数：

CREATE OR REPLACE FUNCTION insert_if_changed()
RETURNS TRIGGER AS $$
DECLARE
    last_record_val bigint;
BEGIN
    -- 获取目标表中最新的计数器值
    SELECT value INTO last_record_val
    FROM foobar_counter
    ORDER BY time DESC
    LIMIT 1;

    -- 若目标表为空（首次插入），或新值与最后一条记录不同，则执行插入
    IF last_record_val IS NULL OR last_record_val IS DISTINCT FROM NEW.fCounter THEN
        INSERT INTO foobar_counter (time, value)
        VALUES (NEW.time, NEW.fCounter);
    END IF;
    RETURN NULL; -- 不影响原modbus表的插入操作
END;
$$ LANGUAGE plpgsql;

2. 创建触发器关联该函数：

CREATE TRIGGER insert_trigger
BEFORE INSERT ON modbus
FOR EACH ROW
EXECUTE FUNCTION insert_if_changed();

3. （可选）为提升查询最新记录的性能，给目标表的time字段创建降序索引：

CREATE INDEX idx_foobar_counter_time ON foobar_counter (time DESC);

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