替换并跟踪旧值

Question

我有一个包含600,000行属性列表的提要，我希望每天将其放入一个表中。但是我还想将旧列(或整行)插入到另一个表中，以便跟踪数据中的更改。例如，我需要知道某些房地产价格在前一年是如何演变的。

upsert的问题是需要指定所有字段，因为我想跟踪每个列的更改，所以"ON CONFLICT"子句会变得太复杂:

INSERT INTO prop_listings (id, prop_type, price, rooms)
VALUES
(33, 'house', 60000, 4)
, (22, 'apartment', 30000, 2)
ON CONFLICT (id, prop_type)  -- complicated clause
DO UPDATE SET
prop_type = EXCLUDED.price_usd
, price = EXCLUDED.volume_24h
...;

另一个问题是跟踪另一个表中的旧值(正在更新的列)。我读到过这可以用触发器来完成。

所以我在想这是不是最好的方法，还是我还遗漏了什么。

Answer 1

不要列出ON CONFLICT子句中的所有列，只列出id(或构成主键的任何列)。这样就更简单了:

INSERT INTO prop_listings AS p (id, prop_type, price, rooms)
VALUES
(33, 'house', 60000, 4)
, (22, 'apartment', 30000, 2)
ON CONFLICT (id)  -- simple clause
DO UPDATE
SET    prop_type = EXCLUDED.price_usd
, price     = EXCLUDED.volume_24h
, rooms     = EXCLUDED.rooms
WHERE (p.prop_type, p.price, p.rooms) IS DISTINCT FROM
(EXCLUDED.prop_type, EXCLUDED.price, EXCLUDED.rooms);

我添加了一个WHERE子句来跳过不会改变任何东西的空更新-也跳过下面的触发器。如果大多数新行版本没有改变，这可能会产生巨大的差异。看到:

• 我如何(或我可以)在多列上选择DISTINCT ?

要跟踪旧的行版本，您可以创建一个具有相同结构的备份表(我们称之为prop_backup)，并添加一个触发器，如:

CREATE OR REPLACE FUNCTION trg_prop_backup()
RETURNS trigger
LANGUAGE plpgsql AS
$func$
BEGIN
INSERT INTO prop_backup  -- table has same columns!
VALUES (OLD.*);

RETURN NEW;
END
$func$;
CREATE TRIGGER prop_listings_upd_bef
BEFORE UPDATE ON prop_listings
FOR EACH ROW EXECUTE FUNCTION trg_prop_backup();

相关:

• 对于每个语句触发器示例
• 触发功能不存在，但我很确定它确实存在